<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222"><meta name="generator" content="Hexo 7.1.1">

  <link rel="apple-touch-icon" sizes="180x180" href="/images/avatar.jpg">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/avatar.jpg">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/avatar.jpg">
  <link rel="mask-icon" href="/images/avatar.jpg" color="#222">
  <meta name="msvalidate.01" content="4AF2328B2035748153A041B7EEF3D15C">

<link rel="stylesheet" href="/css/main.css">

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Noto+Serif+SC:300,300italic,400,400italic,700,700italic%7CLobster:300,300italic,400,400italic,700,700italic%7CPingFang+SC:300,300italic,400,400italic,700,700italic%7CRoboto+Mono:300,300italic,400,400italic,700,700italic&display=swap&subset=latin,latin-ext">

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.1/css/all.min.css" integrity="sha256-wiz7ZSCn/btzhjKDQBms9Hx4sSeUYsDrTLg7roPstac=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.1.1/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/fancyapps-ui/5.0.33/fancybox/fancybox.css" integrity="sha256-gkQVf8UKZgQ0HyuxL/VnacadJ+D2Kox2TCEBuNQg5+w=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"ethanliu6.github.io","root":"/","images":"/images","scheme":"Gemini","darkmode":false,"version":"8.19.2","exturl":true,"sidebar":{"position":"left","display":"always","padding":12,"offset":18},"copycode":{"enable":true,"style":"mac"},"fold":{"enable":true,"height":300},"bookmark":{"enable":true,"color":"#222","save":"manual"},"mediumzoom":true,"lazyload":true,"pangu":false,"comments":{"style":"buttons","active":"changyan","storage":true,"lazyload":false,"nav":null,"activeClass":"changyan"},"stickytabs":false,"motion":{"enable":true,"async":false,"transition":{"menu_item":"fadeInDown","post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"},"path":"/search.xml","localsearch":{"enable":true,"trigger":"auto","top_n_per_article":6,"unescape":false,"preload":false}}</script><script src="/js/config.js"></script>

    <meta name="description" content="这是一篇关于Java中MySQL的JDBC">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL之JDBC">
<meta property="og:url" content="http://ethanliu6.github.io/2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0&%E7%BB%BC%E5%90%88%E7%BB%83%E4%B9%A0/index.html">
<meta property="og:site_name" content="&lt;font color&#x3D;&#39;white&#39; &gt;Ethan の Blog&lt;&#x2F;font&gt;">
<meta property="og:description" content="这是一篇关于Java中MySQL的JDBC">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/%E7%94%B5%E8%84%91%E5%92%8C%E5%85%B6%E4%BB%96%E7%A1%AC%E4%BB%B6%E8%AE%BE%E5%A4%87%E4%BA%A4%E4%BA%92.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%93.bmp">
<meta property="og:image" content="http://ethanliu6.github.io/2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0&%E7%BB%BC%E5%90%88%E7%BB%83%E4%B9%A0/img/jdbc03.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%931.bmp">
<meta property="og:image" content="http://ethanliu6.github.io/Users/ethan.liu/Desktop/02_CodeData/Code#016%2003-%E6%95%B0%E6%8D%AE%E5%BA%93%E9%98%B6%E6%AE%B5%E8%AF%BE%E4%BB%B6/%E6%95%B0%E6%8D%AE%E5%BA%93_day04/01_%E8%AF%BE%E4%BB%B6/JDBC/%E8%AE%B2%E4%B9%89/img\jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%932.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%933.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%934.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/image-20210720143950477.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%935.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%936.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%937.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc07.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc08.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc10.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc09.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc13.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/url.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc14.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc15.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc18.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc19.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/%E7%BB%93%E6%9E%9C%E9%9B%86.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/image-20210720164733101.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/sql%E6%B3%A8%E5%85%A5%E9%97%AE%E9%A2%98.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/15.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/JDBC%E6%8E%A7%E5%88%B6%E4%BA%8B%E5%8A%A1.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/%E4%B8%89%E5%B1%82%E5%BC%80%E5%8F%91%E6%B5%81%E7%A8%8B%E5%9B%BE.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E6%93%8D%E4%BD%9C%E6%95%B0%E6%8D%AE%E5%BA%93%E5%9B%BE%E8%A7%A3.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0%E5%8E%9F%E7%90%86.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/1550388331344.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/11.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/12.bmp">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/image-20200609205210845.png">
<meta property="og:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/image-20210917112318189.png">
<meta property="article:published_time" content="2024-05-02T16:00:00.000Z">
<meta property="article:modified_time" content="2024-05-17T17:15:08.635Z">
<meta property="article:author" content="Ethan Liu">
<meta property="article:tag" content="Java">
<meta property="article:tag" content="MySQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/%E7%94%B5%E8%84%91%E5%92%8C%E5%85%B6%E4%BB%96%E7%A1%AC%E4%BB%B6%E8%AE%BE%E5%A4%87%E4%BA%A4%E4%BA%92.bmp">


<link rel="canonical" href="http://ethanliu6.github.io/2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0&%E7%BB%BC%E5%90%88%E7%BB%83%E4%B9%A0/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":false,"isPost":true,"lang":"zh-CN","comments":true,"permalink":"http://ethanliu6.github.io/2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0&%E7%BB%BC%E5%90%88%E7%BB%83%E4%B9%A0/","path":"2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&数据库连接池&综合练习/","title":"MySQL之JDBC"}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>MySQL之JDBC | <font color='white' >Ethan の Blog</font></title>
  








  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>
<style>.darkmode--activated{--body-bg-color:#282828;--content-bg-color:#333;--card-bg-color:#555;--text-color:#ccc;--blockquote-color:#bbb;--link-color:#ccc;--link-hover-color:#eee;--brand-color:#ddd;--brand-hover-color:#ddd;--table-row-odd-bg-color:#282828;--table-row-hover-bg-color:#363636;--menu-item-bg-color:#555;--btn-default-bg:#222;--btn-default-color:#ccc;--btn-default-border-color:#555;--btn-default-hover-bg:#666;--btn-default-hover-color:#ccc;--btn-default-hover-border-color:#666;--highlight-background:#282b2e;--highlight-foreground:#a9b7c6;--highlight-gutter-background:#34393d;--highlight-gutter-foreground:#9ca9b6}.darkmode--activated img{opacity:.75}.darkmode--activated img:hover{opacity:.9}.darkmode--activated code{color:#69dbdc;background:0 0}button.darkmode-toggle{z-index:9999}.darkmode-ignore,img{display:flex!important}.beian img{display:inline-block!important}</style></head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <div class="column">
      <header class="header" itemscope itemtype="http://schema.org/WPHeader"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <p class="site-title"><font color='white' >Ethan の Blog</font></p>
      <i class="logo-line"></i>
    </a>
      <p class="site-subtitle" itemprop="description"><font color='#FDABC1' size=3>清醒的停留，胜过——盲目的前行</font></p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger" aria-label="搜索" role="button">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li><li class="menu-item menu-item-关于我"><a href="/aboutme/" rel="section"><i class="fa fa-user fa-fw"></i>关于我</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签<span class="badge">18</span></a></li><li class="menu-item menu-item-categories"><a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类<span class="badge">14</span></a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档<span class="badge">53</span></a></li><li class="menu-item menu-item-love"><a href="/categories/Love/" rel="section"><i class="fas fa-heart fa-fw"></i>Love</a></li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup"><div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off" maxlength="80"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close" role="button">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div class="search-result-container no-result">
  <div class="search-result-icon">
    <i class="fa fa-spinner fa-pulse fa-5x"></i>
  </div>
</div>

    </div>
  </div>

</header>
        
  
  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#%E7%AC%AC%E4%B8%80%E7%AB%A0-jdbc"><span class="nav-number">1.</span> <span class="nav-text">第一章 JDBC</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#jdbc%E7%9A%84%E6%A6%82%E5%BF%B5"><span class="nav-number">1.1.</span> <span class="nav-text">JDBC的概念</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87"><span class="nav-number">1.1.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3"><span class="nav-number">1.1.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#jdbc%E7%9A%84%E7%94%B1%E6%9D%A5"><span class="nav-number">1.1.2.1.</span> <span class="nav-text">JDBC的由来</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#jdbc%E7%9A%84%E5%A5%BD%E5%A4%84"><span class="nav-number">1.1.2.2.</span> <span class="nav-text">JDBC的好处</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B0%8F%E7%BB%93"><span class="nav-number">1.1.3.</span> <span class="nav-text">小结</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#jdbc%E6%A0%B8%E5%BF%83api%E7%9A%84%E4%BB%8B%E7%BB%8D"><span class="nav-number">1.2.</span> <span class="nav-text">JDBC核心API的介绍</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-1"><span class="nav-number">1.2.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-1"><span class="nav-number">1.2.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#jdbc%E4%BC%9A%E7%94%A8%E5%88%B0%E7%9A%84%E5%8C%85"><span class="nav-number">1.2.2.1.</span> <span class="nav-text">JDBC会用到的包</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#jdbc%E5%9B%9B%E4%B8%AA%E6%A0%B8%E5%BF%83%E5%AF%B9%E8%B1%A1"><span class="nav-number">1.2.2.2.</span> <span class="nav-text">JDBC四个核心对象</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#jdbc%E8%AE%BF%E9%97%AE%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%AD%A5%E9%AA%A4"><span class="nav-number">1.2.2.3.</span> <span class="nav-text">JDBC访问数据库的步骤</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B0%8F%E7%BB%93-1"><span class="nav-number">1.2.3.</span> <span class="nav-text">小结</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%BF%AB%E9%80%9F%E5%85%A5%E9%97%A8"><span class="nav-number">1.2.4.</span> <span class="nav-text">快速入门：</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#jdbc%E6%B3%A8%E5%86%8C%E9%A9%B1%E5%8A%A8"><span class="nav-number">1.3.</span> <span class="nav-text">JDBC注册驱动</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-2"><span class="nav-number">1.3.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-2"><span class="nav-number">1.3.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%87%86%E5%A4%87%E5%B7%A5%E4%BD%9C"><span class="nav-number">1.3.2.1.</span> <span class="nav-text">准备工作</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%AF%BC%E5%85%A5%E9%A9%B1%E5%8A%A8jar%E5%8C%85"><span class="nav-number">1.3.2.2.</span> <span class="nav-text">导入驱动Jar包</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%B3%A8%E5%86%8C%E9%A9%B1%E5%8A%A8"><span class="nav-number">1.3.2.3.</span> <span class="nav-text">注册驱动</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#api%E4%BB%8B%E7%BB%8D"><span class="nav-number">1.3.2.3.1.</span> <span class="nav-text">API介绍</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8%E6%AD%A5%E9%AA%A4"><span class="nav-number">1.3.2.3.2.</span> <span class="nav-text">使用步骤</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#%E6%A1%88%E4%BE%8B%E4%BB%A3%E7%A0%81"><span class="nav-number">1.3.2.3.3.</span> <span class="nav-text">案例代码</span></a></li></ol></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B0%8F%E7%BB%93-2"><span class="nav-number">1.3.3.</span> <span class="nav-text">小结</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%8E%B7%E5%8F%96%E8%BF%9E%E6%8E%A5"><span class="nav-number">1.4.</span> <span class="nav-text">获取连接</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-3"><span class="nav-number">1.4.1.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#api%E4%BB%8B%E7%BB%8D-1"><span class="nav-number">1.4.1.1.</span> <span class="nav-text">API介绍</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%8F%82%E6%95%B0%E8%AF%B4%E6%98%8E"><span class="nav-number">1.4.1.2.</span> <span class="nav-text">参数说明</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%B3%A8%E6%84%8F%E4%BA%8B%E9%A1%B9"><span class="nav-number">1.4.1.3.</span> <span class="nav-text">注意事项</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8%E6%AD%A5%E9%AA%A4-1"><span class="nav-number">1.4.1.4.</span> <span class="nav-text">使用步骤</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%A1%88%E4%BE%8B%E4%BB%A3%E7%A0%81-1"><span class="nav-number">1.4.1.5.</span> <span class="nav-text">案例代码</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%A1%88%E4%BE%8B%E6%95%88%E6%9E%9C"><span class="nav-number">1.4.1.6.</span> <span class="nav-text">案例效果</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B0%8F%E7%BB%93-3"><span class="nav-number">1.4.2.</span> <span class="nav-text">小结</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#jdbc%E5%AE%9E%E7%8E%B0%E5%AF%B9%E5%8D%95%E8%A1%A8%E6%95%B0%E6%8D%AE%E5%A2%9E%E5%88%A0%E6%94%B9"><span class="nav-number">1.5.</span> <span class="nav-text">JDBC实现对单表数据增、删、改</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-3"><span class="nav-number">1.5.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-4"><span class="nav-number">1.5.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#jdbc%E5%AE%9E%E7%8E%B0%E5%AF%B9%E5%8D%95%E8%A1%A8%E6%95%B0%E6%8D%AE%E5%A2%9E%E5%88%A0%E6%94%B9-1"><span class="nav-number">1.5.2.1.</span> <span class="nav-text">JDBC实现对单表数据增、删、改</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#api%E4%BB%8B%E7%BB%8D-2"><span class="nav-number">1.5.2.1.1.</span> <span class="nav-text">API介绍</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8%E6%AD%A5%E9%AA%A4-2"><span class="nav-number">1.5.2.2.</span> <span class="nav-text">使用步骤</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%A1%88%E4%BE%8B%E4%BB%A3%E7%A0%81-2"><span class="nav-number">1.5.2.3.</span> <span class="nav-text">案例代码</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#jdbc%E5%AE%9E%E7%8E%B0%E5%AF%B9%E5%8D%95%E8%A1%A8%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2"><span class="nav-number">1.6.</span> <span class="nav-text">JDBC实现对单表数据查询</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-4"><span class="nav-number">1.6.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-5"><span class="nav-number">1.6.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#resultset%E7%9A%84%E5%8E%9F%E7%90%86"><span class="nav-number">1.6.2.1.</span> <span class="nav-text">ResultSet的原理</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#resultset%E8%8E%B7%E5%8F%96%E6%95%B0%E6%8D%AE%E7%9A%84api"><span class="nav-number">1.6.2.2.</span> <span class="nav-text">ResultSet获取数据的API</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8jdbc%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E7%9A%84%E6%95%B0%E6%8D%AE%E7%9A%84%E6%AD%A5%E9%AA%A4"><span class="nav-number">1.6.2.3.</span> <span class="nav-text">使用JDBC查询数据库中的数据的步骤</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%A1%88%E4%BE%8B%E4%BB%A3%E7%A0%81-3"><span class="nav-number">1.6.2.4.</span> <span class="nav-text">案例代码</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B0%8F%E7%BB%93-4"><span class="nav-number">1.6.3.</span> <span class="nav-text">小结</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%A0%B9%E6%8D%AE%E5%88%AB%E5%90%8D%E6%9F%A5%E8%AF%A2%E5%A4%84%E7%90%86%E7%BB%93%E6%9E%9C%E9%9B%86%E6%89%A9%E5%B1%95"><span class="nav-number">1.7.</span> <span class="nav-text">根据别名查询处理结果集扩展</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#jdbc%E5%AE%9E%E7%8E%B0%E7%99%BB%E5%BD%95%E6%A1%88%E4%BE%8B"><span class="nav-number">1.8.</span> <span class="nav-text">JDBC实现登录案例</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-5"><span class="nav-number">1.8.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-6"><span class="nav-number">1.8.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%A1%88%E4%BE%8B%E5%88%86%E6%9E%90"><span class="nav-number">1.8.2.1.</span> <span class="nav-text">案例分析</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%AE%9E%E7%8E%B0%E6%AD%A5%E9%AA%A4"><span class="nav-number">1.8.2.2.</span> <span class="nav-text">实现步骤</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B0%8F%E7%BB%93-5"><span class="nav-number">1.8.3.</span> <span class="nav-text">小结</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#preparedsatement%E9%A2%84%E7%BC%96%E8%AF%91%E5%AF%B9%E8%B1%A1"><span class="nav-number">1.9.</span> <span class="nav-text">PreparedSatement预编译对象</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-6"><span class="nav-number">1.9.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-7"><span class="nav-number">1.9.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#sql%E6%B3%A8%E5%85%A5%E9%97%AE%E9%A2%98"><span class="nav-number">1.9.2.1.</span> <span class="nav-text">SQL注入问题</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#sql%E6%B3%A8%E5%85%A5%E5%8E%9F%E5%9B%A0%E6%BC%94%E7%A4%BA_%E6%A8%A1%E6%8B%9F%E7%99%BB%E9%99%86"><span class="nav-number">1.9.2.2.</span> <span class="nav-text">sql注入原因演示_模拟登陆：</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B0%8F%E7%BB%93-6"><span class="nav-number">1.9.3.</span> <span class="nav-text">小结</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#preparedstatement%E8%A7%A3%E5%86%B3sql%E6%B3%A8%E5%85%A5%E6%96%B9%E6%A1%88"><span class="nav-number">1.10.</span> <span class="nav-text">PreparedStatement解决SQL注入方案</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-7"><span class="nav-number">1.10.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-8"><span class="nav-number">1.10.2.</span> <span class="nav-text">讲解</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#preparedstatement%E7%9A%84-%E5%BA%94%E7%94%A8%E6%8E%8C%E6%8F%A1"><span class="nav-number">1.11.</span> <span class="nav-text">PreparedStatement的
应用(掌握)</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-8"><span class="nav-number">1.11.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-9"><span class="nav-number">1.11.2.</span> <span class="nav-text">讲解</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%89%A7%E8%A1%8Cdql%E5%B0%81%E8%A3%85%E6%88%90%E9%9B%86%E5%90%88%E7%9A%84%E6%93%8D%E4%BD%9C"><span class="nav-number">1.12.</span> <span class="nav-text">执行DQL封装成集合的操作</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#jdbc%E4%BA%8B%E5%8A%A1"><span class="nav-number">1.13.</span> <span class="nav-text">JDBC事务</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-9"><span class="nav-number">1.13.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-10"><span class="nav-number">1.13.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%87%86%E5%A4%87%E6%95%B0%E6%8D%AE"><span class="nav-number">1.13.2.1.</span> <span class="nav-text">准备数据</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#jdbc%E6%93%8D%E4%BD%9C%E4%BA%8B%E5%8A%A1"><span class="nav-number">1.13.2.2.</span> <span class="nav-text">JDBC操作事务</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#api%E4%BB%8B%E7%BB%8D-3"><span class="nav-number">1.13.2.3.</span> <span class="nav-text">API介绍</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B0%8F%E7%BB%93-7"><span class="nav-number">1.13.3.</span> <span class="nav-text">小结</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#jdbc%E5%B7%A5%E5%85%B7%E7%B1%BB"><span class="nav-number">1.14.</span> <span class="nav-text">JDBC工具类</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E4%B8%89%E5%B1%82%E5%BC%80%E5%8F%91%E4%B8%9A%E5%8A%A1%E7%9A%84%E6%A1%88%E4%BE%8B%E5%88%86%E6%9E%90%E6%8E%8C%E6%8F%A1"><span class="nav-number">1.15.</span> <span class="nav-text">三层开发业务的案例分析(掌握)</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E7%AC%AC%E4%BA%8C%E7%AB%A0-%E8%BF%9E%E6%8E%A5%E6%B1%A0"><span class="nav-number">2.</span> <span class="nav-text">第二章 连接池</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%BF%9E%E6%8E%A5%E6%B1%A0%E4%BB%8B%E7%BB%8D"><span class="nav-number">2.1.</span> <span class="nav-text">连接池介绍</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-10"><span class="nav-number">2.1.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-11"><span class="nav-number">2.1.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%B2%A1%E6%9C%89%E8%BF%9E%E6%8E%A5%E6%B1%A0%E7%9A%84%E7%8E%B0%E7%8A%B6"><span class="nav-number">2.1.2.1.</span> <span class="nav-text">没有连接池的现状</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%BF%9E%E6%8E%A5%E6%B1%A0%E8%A7%A3%E5%86%B3%E7%8E%B0%E7%8A%B6%E9%97%AE%E9%A2%98%E7%9A%84%E5%8E%9F%E7%90%86"><span class="nav-number">2.1.2.2.</span> <span class="nav-text">连接池解决现状问题的原理</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%BF%9E%E6%8E%A5%E6%B1%A0%E5%A5%BD%E5%A4%84"><span class="nav-number">2.1.2.3.</span> <span class="nav-text">连接池好处</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%B8%B8%E7%94%A8%E8%BF%9E%E6%8E%A5%E6%B1%A0%E7%9A%84%E4%BB%8B%E7%BB%8D"><span class="nav-number">2.1.2.4.</span> <span class="nav-text">常用连接池的介绍</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B0%8F%E7%BB%93-8"><span class="nav-number">2.1.3.</span> <span class="nav-text">小结</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#druid%E8%BF%9E%E6%8E%A5%E6%B1%A0"><span class="nav-number">2.2.</span> <span class="nav-text">Druid连接池</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%9B%AE%E6%A0%87-11"><span class="nav-number">2.2.1.</span> <span class="nav-text">目标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%B2%E8%A7%A3-12"><span class="nav-number">2.2.2.</span> <span class="nav-text">讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#druid%E7%AE%80%E4%BB%8B"><span class="nav-number">2.2.2.1.</span> <span class="nav-text">DRUID简介</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#druid%E5%B8%B8%E7%94%A8%E7%9A%84%E9%85%8D%E7%BD%AE%E5%8F%82%E6%95%B0"><span class="nav-number">2.2.2.2.</span> <span class="nav-text">Druid常用的配置参数</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#druid%E8%BF%9E%E6%8E%A5%E6%B1%A0%E5%9F%BA%E6%9C%AC%E4%BD%BF%E7%94%A8"><span class="nav-number">2.2.2.3.</span> <span class="nav-text">Druid连接池基本使用</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#api%E4%BB%8B%E7%BB%8D-4"><span class="nav-number">2.2.2.3.1.</span> <span class="nav-text">API介绍</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8%E6%AD%A5%E9%AA%A4-3"><span class="nav-number">2.2.2.4.</span> <span class="nav-text">使用步骤</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%A1%88%E4%BE%8B%E4%BB%A3%E7%A0%81-4"><span class="nav-number">2.2.2.5.</span> <span class="nav-text">案例代码</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%A1%88%E4%BE%8B%E6%95%88%E6%9E%9C-1"><span class="nav-number">2.2.2.6.</span> <span class="nav-text">案例效果</span></a></li></ol></li></ol></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E7%AC%AC%E4%B8%89%E7%AB%A0-jdbc%E7%BB%83%E4%B9%A0"><span class="nav-number">3.</span> <span class="nav-text">第三章 JDBC练习</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E9%9C%80%E6%B1%82"><span class="nav-number">3.1.</span> <span class="nav-text">需求</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%8E%AF%E5%A2%83%E6%90%AD%E5%BB%BA"><span class="nav-number">3.2.</span> <span class="nav-text">环境搭建</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E6%89%80%E6%9C%89%E6%95%B0%E6%8D%AE"><span class="nav-number">3.3.</span> <span class="nav-text">查询所有数据</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%B7%BB%E5%8A%A0"><span class="nav-number">3.4.</span> <span class="nav-text">添加</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E4%BF%AE%E6%94%B9"><span class="nav-number">3.5.</span> <span class="nav-text">修改</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%88%A0%E9%99%A4"><span class="nav-number">3.6.</span> <span class="nav-text">删除</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%BB%BC%E5%90%88%E6%A1%88%E4%BE%8B"><span class="nav-number">3.7.</span> <span class="nav-text">综合案例</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#sql%E4%B8%AD%E7%9A%84brand%E8%A1%A8"><span class="nav-number">3.7.1.</span> <span class="nav-text">SQL中的Brand表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#brand%E5%AE%9E%E4%BD%93%E7%B1%BB"><span class="nav-number">3.7.2.</span> <span class="nav-text">Brand实体类</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#branddao%E7%B1%BB"><span class="nav-number">3.7.3.</span> <span class="nav-text">BrandDao类</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%B5%8B%E8%AF%95%E4%BB%A3%E7%A0%81"><span class="nav-number">3.7.4.</span> <span class="nav-text">测试代码</span></a></li></ol></li></ol></li></ol></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="Ethan Liu"
      src="/images/avatar.jpg">
  <p class="site-author-name" itemprop="name">Ethan Liu</p>
  <div class="site-description" itemprop="description"><font color='#0000FF' size=1.5px>语言学 | NLP | Py | Java</font></div>
</div>
<div class="site-state-wrap animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives/">
          <span class="site-state-item-count">53</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories/">
        <span class="site-state-item-count">14</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags/">
        <span class="site-state-item-count">18</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author animated">
      <span class="links-of-author-item">
        <span class="exturl" data-url="aHR0cHM6Ly9naXRodWIuY29tL2V0aGFubGl1Ng==" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;ethanliu6"><i class="fab fa-github fa-fw"></i>GitHub</span>
      </span>
      <span class="links-of-author-item">
        <span class="exturl" data-url="bWFpbHRvOmxpdWUyMzM2QGdtYWlsLmNvbQ==" title="E-Mail → mailto:liue2336@gmail.com"><i class="fa fa-envelope fa-fw"></i>E-Mail</span>
      </span>
      <span class="links-of-author-item">
        <span class="exturl" data-url="aHR0cHM6Ly9zcGFjZS5iaWxpYmlsaS5jb20vMTMyNzA5OTk3Nw==" title="BiliBili → https:&#x2F;&#x2F;space.bilibili.com&#x2F;1327099977"><i class="fa-brands fa-bilibili fa-fw"></i>BiliBili</span>
      </span>
      <span class="links-of-author-item">
        <a href="/categories/Love/" title="Love → &#x2F;categories&#x2F;Love&#x2F;" rel="noopener me"><i class="fa fa-heart fa-fw"></i>Love</a>
      </span>
  </div>

        </div>
      </div>
    </div>

    
  </aside>


    </div>

    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="http://ethanliu6.github.io/2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0&%E7%BB%BC%E5%90%88%E7%BB%83%E4%B9%A0/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.jpg">
      <meta itemprop="name" content="Ethan Liu">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="<font color='white' >Ethan の Blog</font>">
      <meta itemprop="description" content="<font color='#0000FF' size=1.5px>语言学 | NLP | Py | Java</font>">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="MySQL之JDBC | <font color='white' >Ethan の Blog</font>">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          MySQL之JDBC<span class="exturl post-edit-link" data-url="aHR0cHM6Ly9naXRodWIuY29tL0V0aGFuTGl1Ni9ldGhhbmxpdTYuZ2l0aHViLmlvL3RyZWUvbWFzdGVyL19wb3N0cy9KYXZhTm90ZXMvTXlTUUwvZGF5MDMvZGF5MDMtSkRCQybmlbDmja7lupPov57mjqXmsaAm57u85ZCI57uD5LmgLm1k" title="编辑"><i class="fa fa-pen-nib"></i></span>
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2024-05-03 00:00:00" itemprop="dateCreated datePublished" datetime="2024-05-03T00:00:00+08:00">2024-05-03</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2024-05-18 01:15:08" itemprop="dateModified" datetime="2024-05-18T01:15:08+08:00">2024-05-18</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/MySQL/" itemprop="url" rel="index"><span itemprop="name">MySQL</span></a>
        </span>
    </span>

  
    <span class="post-meta-item" title="阅读次数" id="busuanzi_container_page_pv">
      <span class="post-meta-item-icon">
        <i class="far fa-eye"></i>
      </span>
      <span class="post-meta-item-text">阅读次数：</span>
      <span id="busuanzi_value_page_pv"></span>
    </span>
  
  <span class="post-meta-item">
    
      <span class="post-meta-item-icon">
        <i class="far fa-comment"></i>
      </span>
      <span class="post-meta-item-text">Changyan：</span>
    
    <a title="MySQL之JDBC" href="/2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0&%E7%BB%BC%E5%90%88%E7%BB%83%E4%B9%A0/#SOHUCS" itemprop="discussionUrl">
      <span id="sourceId::020300feda6e9b7c9ef118b4bbf6cd7c" class="cy_cmt_count" itemprop="commentCount"></span>
    </a>
  </span>
  
  
    <span class="post-meta-break"></span>
    <span class="post-meta-item" title="本文字数">
      <span class="post-meta-item-icon">
        <i class="far fa-file-word"></i>
      </span>
      <span class="post-meta-item-text">本文字数：</span>
      <span>6.7k</span>
    </span>
    <span class="post-meta-item" title="阅读时长">
      <span class="post-meta-item-icon">
        <i class="far fa-clock"></i>
      </span>
      <span class="post-meta-item-text">阅读时长 &asymp;</span>
      <span>24 分钟</span>
    </span>
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody"><blockquote class="blockquote-center">
<p>这是一篇关于Java中MySQL的JDBC</p>

</blockquote>
<span id="more"></span>
<h1 id="第一章-jdbc">第一章 JDBC</h1>
<h2 id="jdbc的概念">JDBC的概念</h2>
<h3 id="目标">目标</h3>
<ol type="1">
<li>能够掌握JDBC的概念</li>
<li>能够理解JDBC的作用</li>
</ol>
<h3 id="讲解">讲解</h3>
<p>客户端操作MySQL数据库的方式</p>
<ol type="1">
<li><p>使用第三方客户端来访问MySQL：SQLyog、Navicat</p></li>
<li><p>使用MySQL自带的命令行方式</p></li>
<li><p><strong>通过Java来访问MySQL数据库，今天要学习的内容</strong></p>
<p><strong>如何通过Java代码去操作数据库呢？</strong></p>
<p>Java中给我们提供专门的一套技术，可以通过其中的某些类和接口操作任何的数据库，这个技术就是今天要学习的JDBC。</p>
<p><strong>什么是JDBC</strong>：Sun公司为了简化、统一对数据库的操作，定义了一套java操作数据库的接口的规范，称之为JDBC。JDBC的全称为：java
database connection (java和 数据库的连接 )
就是使用java代码来操作数据库。
<strong>JDBC的作用</strong>：Java通过JDBC就可以操作数据库</p></li>
</ol>
<h4 id="jdbc的由来">JDBC的由来</h4>
<ol type="1">
<li><p><strong>java程序依赖于jdk,jdk和数据库是2个不同的应用程序，那他们是怎么进行访问的呢？</strong></p>
<p>要想搞清楚这个问题，我们必须了解下电脑是如何和其他硬件设备交互的。假设我们电脑安装完系统之后是一个无驱动的操作系统。那么当我们电脑想播放声音，即安装音响，必须得安装声卡驱动。同时电脑想和u盘硬件进行交互，也必须在电脑上安装对应的驱动。如果驱动安装失败，很显然他们是不能正常的交互的。这个安装驱动的其实就是为了定义他们两个相互交互的规则。只有统一了规则，才能交互。</p>
<p>具体的解释如下图所示：</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/%E7%94%B5%E8%84%91%E5%92%8C%E5%85%B6%E4%BB%96%E7%A1%AC%E4%BB%B6%E8%AE%BE%E5%A4%87%E4%BA%A4%E4%BA%92.bmp"></p></li>
</ol>
<p>同理：java程序想和数据库进行交互，也必须得安装数据库驱动，这样才能交互。但是，我们数据库有多种，这样就会导致不同的数据库具备不同的数据库驱动。</p>
<p>从而会导致在开发中作为程序员由于安装不同的数据库驱动而浪费很多时间，同时和数据库交互的java代码也得重新书写，这样都会导致效率很低。所以说，sun公司就会制定一套规则，这套规则就是用来java程序连接数据库的，然后各大数据库厂商只需要实现这个规则即可。这个规则就是jdbc技术，即接口。</p>
<p>换句话就是说，就是数据库厂商使用sun公司提供的接口，然后作为java程序员实现接口中的方法即可。<strong>接口中的方法体由具体的数据库厂商来实现。</strong></p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%93.bmp"></p>
<h4 id="jdbc的好处">JDBC的好处</h4>
<ol type="1">
<li>我们只需要会调用JDBC接口中的方法即可，使用简单</li>
<li>JDBC有关的类和接口：都在java.sql 和 javax.sql(扩展包) 包下</li>
<li>方法体由具体的数据库厂商来完成的</li>
<li>使用同一套Java代码，进行少量的修改就可以访问其他JDBC支持的数据库</li>
</ol>
<h3 id="小结">小结</h3>
<ol type="1">
<li>说出JDBC的概念？Java数据库连接(Java操作数据库的标准规范)</li>
<li>说出JDBC的作用？Java通过JDBC就可以操作数据库</li>
</ol>
<h2 id="jdbc核心api的介绍">JDBC核心API的介绍</h2>
<h3 id="目标-1">目标</h3>
<p>能够理解JDBC四个核心对象</p>
<h3 id="讲解-1">讲解</h3>
<h4 id="jdbc会用到的包">JDBC会用到的包</h4>
<ol type="1">
<li>java.sql：JDBC访问数据库的基础包，在JavaSE中的包。如：java.sql.Connection</li>
<li>javax.sql： JDBC访问数据库的扩展包</li>
<li>数据库的驱动，各大数据库厂商来实现。如：MySQL的驱动：com.mysql.jdbc.Driver</li>
</ol>
<h4 id="jdbc四个核心对象">JDBC四个核心对象</h4>
<p>这几个类都是在java.sql包中</p>
<ol type="1">
<li>DriverManager(类): 数据库驱动管理类。这个类的作用：1）注册驱动;
2)创建java代码和数据库之间的连接，即获取Connection接口;</li>
<li>Connection(接口): 是一个接口,
建立数据库连接的一个接口。作用：建立数据库和java代码之间的连接。表示与数据库创建的连接</li>
<li>Statement(接口)、PreparedStatement(接口)
(解决安全隐患问题，比如sql注入的问题)：
数据库操作，向数据库发送sql语句。执行SQL语句的对象</li>
<li>ResultSet: 结果集或一张虚拟表。 Statement 发送sql语句，得到的结果
封装在 ResultSet 中。 <img data-src="img/jdbc03.png"></li>
</ol>
<h4 id="jdbc访问数据库的步骤">JDBC访问数据库的步骤</h4>
<ol type="1">
<li><p>由DriverManager注册驱动程序</p></li>
<li><p>创建和数据库的连接对象Connection</p></li>
<li><p>由客户端发送SQL语句给服务器执行，SQL语句封装成Statement对象</p></li>
<li><p>查询到的结果集封装成ResultSet对象</p></li>
<li><p>在客户端可以从ResultSet中取出数据，处理结果集</p></li>
<li><p>释放资源，关闭连接对象</p></li>
</ol>
<h3 id="小结-1">小结</h3>
<p>JDBC四个核心对象？</p>
<ol type="1">
<li>DriverManager(类): 用于注册驱动和获取连接</li>
<li>Connection(接口): 表示与数据库创建的连接</li>
<li>Statement(接口): 执行SQL语句的对象</li>
<li>ResultSet(接口): 结果集或一张虚拟表</li>
</ol>
<h3 id="快速入门">快速入门：</h3>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br></pre></td><td class="code"><pre><span class="line">package ethanliu.jdbc;</span><br><span class="line"></span><br><span class="line">import com.mysql.jdbc.Driver;</span><br><span class="line">import com.sun.source.tree.WhileLoopTree;</span><br><span class="line">import org.junit.Test;</span><br><span class="line"></span><br><span class="line">import java.sql.*;</span><br><span class="line"></span><br><span class="line">/**</span><br><span class="line"> * @Author EthanLiu 16693226842@163.com</span><br><span class="line"> * @Date 2024/5/3 22:14</span><br><span class="line"> * @Project MySQL</span><br><span class="line"> * @Theme JDBC快速入门</span><br><span class="line"> */</span><br><span class="line">public class QuickStart &#123;</span><br><span class="line"></span><br><span class="line">    @Test</span><br><span class="line">    public void quickStart() throws ClassNotFoundException, SQLException &#123;</span><br><span class="line">        // 1.注册驱动(确认要用哪个数据库)-&gt;mysql 8.0</span><br><span class="line">        Class.forName(&quot;com.mysql.cj.jdbc.Driver&quot;); //加载Driver驱动</span><br><span class="line"></span><br><span class="line">        // 2. 连接数据库</span><br><span class="line">        String url = &quot;jdbc:mysql://localhost:3306/JX&quot;;</span><br><span class="line">        //String url = &quot;jdbc:mysql://localhost:3306/JX?useUnicode=true&amp;characterEncoding=utf8&quot;; //解决中文乱码问题</span><br><span class="line">        Connection conn = DriverManager.getConnection(url, &quot;root&quot;, &quot;l20031220&quot;);</span><br><span class="line"></span><br><span class="line">        // 3. 编写SQL语句</span><br><span class="line">        String sql = &quot;Select Sid, Sname, Age, City From Student&quot;;</span><br><span class="line"></span><br><span class="line">        // 4. 发送给数据库(数据库执行SQL语句并返回执行结果)</span><br><span class="line">        Statement stmt = conn.createStatement(); //基于数据库连接对象创建一个数据库操作对象</span><br><span class="line">        ResultSet res = stmt.executeQuery(sql);</span><br><span class="line"></span><br><span class="line">        // 5. 处理SQL的执行结果</span><br><span class="line">        while (res.next())&#123;</span><br><span class="line">            System.out.print(res.getInt(&quot;Sid&quot;) + &quot;\t\t&quot;);</span><br><span class="line">            System.out.print(res.getString(&quot;Sname&quot;) + &quot;\t\t&quot;);</span><br><span class="line">            System.out.print(res.getInt(&quot;Age&quot;) + &quot;\t\t&quot;);</span><br><span class="line">            System.out.println(res.getString(&quot;City&quot;) + &quot;\t\t&quot;);</span><br><span class="line"></span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        // 6. 释放资源(断开和数据库的连接)</span><br><span class="line">        res.close();</span><br><span class="line">        stmt.close();</span><br><span class="line">        conn.close();</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h2 id="jdbc注册驱动">JDBC注册驱动</h2>
<p><code>Connection</code>表示Java程序与数据库之间的连接，只有拿到Connection才能操作数据库。</p>
<p><strong>JDBC获取连接步骤</strong></p>
<p>1.导入mysql驱动Jar包 2.注册驱动 3.获取连接</p>
<h3 id="目标-2">目标</h3>
<p>能够导入mysql驱动Jar包</p>
<p>能够通过JDBC注册数据库驱动</p>
<h3 id="讲解-2">讲解</h3>
<h4 id="准备工作">准备工作</h4>
<p>1、确定启动mysql数据库。</p>
<p>此电脑-------》右键管理-------》选择服务</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%931.bmp"></p>
<p>找到MySQL服务，查看MySQL服务是否已经启动了，如果启动是灰色，说明已经启动了。</p>
<p><img data-src="/Users/ethan.liu/Desktop/02_CodeData/Code#016%2003-数据库阶段课件/数据库_day04/01_课件/JDBC/讲义/img\jdbc连接数据库2.bmp"></p>
<p>2、创建数据库和创建user表，并添加信息，sql语句如下。</p>
<p>打开可视化工具，将如下sql语句添加到可视化工具中，并执行以下sql语句即可。</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%933.bmp"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 创建数据库</span></span><br><span class="line"><span class="keyword">create</span> database day04_db;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 切换数据库</span></span><br><span class="line">use day04_db;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 用户表</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="keyword">user</span> (</span><br><span class="line">  id <span class="type">int</span> <span class="keyword">primary</span> key auto_increment,</span><br><span class="line">  username <span class="type">varchar</span>(<span class="number">30</span>) <span class="keyword">unique</span> <span class="keyword">not</span> <span class="keyword">null</span>,</span><br><span class="line">  password <span class="type">varchar</span>(<span class="number">30</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">user</span>(username, password) <span class="keyword">values</span>(<span class="string">&#x27;zhangsan&#x27;</span>,<span class="string">&#x27;123&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">user</span>(username, password) <span class="keyword">values</span>(<span class="string">&#x27;lisi&#x27;</span>,<span class="string">&#x27;123&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">user</span>(username, password) <span class="keyword">values</span>(<span class="string">&#x27;wangwu&#x27;</span>,<span class="string">&#x27;123&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>
<p>执行结果：</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%934.bmp"></p>
<h4 id="导入驱动jar包">导入驱动Jar包</h4>
<figure>
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/image-20210720143950477.png" alt="image-20210720143950477">
<figcaption aria-hidden="true">image-20210720143950477</figcaption>
</figure>
<p>1、新建项目day04，在项目day04上新建lib文件夹</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%935.bmp"></p>
<p>2、将mysql驱动的jar包导入到lib文件夹下</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%936.bmp"></p>
<p>3、在导入的mysql的jar包上右键，选择Add as Library...</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%937.bmp"></p>
<h4 id="注册驱动">注册驱动</h4>
<p>我们Java程序需要通过数据库驱动才能连接到数据库，因此需要注册驱动。
MySQL的驱动的入口类是：<code>com.mysql.jdbc.Driver</code> <img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc07.png"></p>
<h5 id="api介绍">API介绍</h5>
<p><code>java.sql.DriverManager</code>类用于注册驱动。提供如下方法注册驱动</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">registerDriver</span><span class="params">(Driver driver)</span> </span><br><span class="line">向 DriverManager 注册给定驱动程序。 </span><br></pre></td></tr></table></figure>
<p>说明：</p>
<p>​ 1)执行完上述语句之后，DriverManager就可以管理mysql驱动了。</p>
<p>​ 2)当前的DriverManager.registerDriver(Driver
driver);方法的参数是Driver，这是jdbc的一个接口，所以我们需要给定实现该接口的实现类。如果我们连接的是mysql数据库，那么需要导入mysql数据库提供的包，也就是com.mysql.jdbc.Driver;
下的Driver类。如果我们连接的是oracle数据库，那么需要导入oracle数据库提供的包。</p>
<h5 id="使用步骤">使用步骤</h5>
<p>1.DriverManager.registerDriver(驱动对象); 传入对应参数即可</p>
<h5 id="案例代码">案例代码</h5>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">Demo01</span> &#123;</span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">		<span class="comment">// 注册驱动</span></span><br><span class="line">		DriverManager.registerDriver(<span class="keyword">new</span> <span class="title class_">com</span>.mysql.jdbc.Driver());</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>说明：这里的new Driver()的类Driver就是来自mysql数据库。</p>
<p>通过查询com.mysql.jdbc.Driver源码，我们发现Driver类“主动”将自己进行注册</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">Driver</span> <span class="keyword">extends</span> <span class="title class_">NonRegisteringDriver</span> <span class="keyword">implements</span> <span class="title class_">java</span>.sql.Driver &#123;</span><br><span class="line">    <span class="keyword">static</span> &#123;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            <span class="comment">// 自己自动注册</span></span><br><span class="line">            java.sql.DriverManager.registerDriver(<span class="keyword">new</span> <span class="title class_">Driver</span>());</span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException E) &#123;</span><br><span class="line">            <span class="keyword">throw</span> <span class="keyword">new</span> <span class="title class_">RuntimeException</span>(<span class="string">&quot;Can&#x27;t register driver!&quot;</span>);</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">    <span class="keyword">public</span> <span class="title function_">Driver</span><span class="params">()</span> <span class="keyword">throws</span> SQLException &#123;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc08.png"></p>
<blockquote>
<p>注意：使用<code>DriverManager.registerDriver(new com.mysql.jdbc.Driver());</code>，存在以下方面不足</p>
<ol type="1">
<li>驱动被注册两次</li>
</ol>
</blockquote>
<p>使用<code>Class.forName("com.mysql.jdbc.Driver");</code>加载驱动，这样驱动只会注册一次</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br></pre></td><td class="code"><pre><span class="line">public class Demo01 &#123;</span><br><span class="line">	public <span class="keyword">static</span> void main(String[] args) throws Exception &#123;</span><br><span class="line">	<span class="comment">/*</span></span><br><span class="line"><span class="comment">            1）注册驱动使用的类是驱动管理类：DriverManager，使用该类中的静态方法：</span></span><br><span class="line"><span class="comment">              static void registerDriver(Driver driver) 向 DriverManager 注册给定驱动程序。</span></span><br><span class="line"><span class="comment">                真正注册驱动代码：  DriverManager.registerDriver(new Driver());</span></span><br><span class="line"><span class="comment">            2) static void registerDriver(Driver driver) 方法参数Driver是驱动接口，java.sql.Driver表示每个驱动程序类必须实现的接口。</span></span><br><span class="line"><span class="comment">                    对于该参数我们都是创建该接口java.sql.Driver实现类对象，实现类由各大数据库厂商来提供，并且必须实现该驱动接口java.sql.Driver</span></span><br><span class="line"><span class="comment">            3） 真正注册驱动代码：  DriverManager.registerDriver(new Driver());</span></span><br><span class="line"><span class="comment">                    注意：这里new Driver()创建的是mysql厂商定义的实现类</span></span><br><span class="line"><span class="comment">                    //com.mysql.jdbc.Driver  属于mysql厂商的实现类</span></span><br><span class="line"><span class="comment">                    //java.sql.Driver  属于sun公司定义的驱动接口</span></span><br><span class="line"><span class="comment">                    public class com.mysql.jdbc.Driver implements java.sql.Driver &#123;</span></span><br><span class="line"><span class="comment">                        public Driver() throws SQLException &#123;</span></span><br><span class="line"><span class="comment">                        &#125;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">                        static &#123;</span></span><br><span class="line"><span class="comment">                            try &#123;</span></span><br><span class="line"><span class="comment">                                DriverManager.registerDriver(new Driver());</span></span><br><span class="line"><span class="comment">                            &#125; catch (SQLException var1) &#123;</span></span><br><span class="line"><span class="comment">                                throw new RuntimeException(&quot;Can&#x27;t register driver!&quot;);</span></span><br><span class="line"><span class="comment">                            &#125;</span></span><br><span class="line"><span class="comment">                        &#125;</span></span><br><span class="line"><span class="comment">                &#125;</span></span><br><span class="line"><span class="comment">            4）为什么我们不使用代码： DriverManager.registerDriver(new Driver()); 注册呢，我们为什么这里使用代码：</span></span><br><span class="line"><span class="comment">                  Class.forName(&quot;com.mysql.jdbc.Driver&quot;);的原因？</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">                原因：因为DriverManager.registerDriver(new Driver());注册了两次，而Class.forName(&quot;com.mysql.jdbc.Driver&quot;);只是注册了一次</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">                 Class.forName(&quot;com.mysql.jdbc.Driver&quot;); 就是将com.mysql.jdbc.Driver类加载到内存中，加载该类，就会执行该类的静态代码块：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">                    public class com.mysql.jdbc.Driver implements java.sql.Driver &#123;</span></span><br><span class="line"><span class="comment">                        //静态代码块，当前类一加载内存就执行</span></span><br><span class="line"><span class="comment">                        static &#123;</span></span><br><span class="line"><span class="comment">                            try &#123;</span></span><br><span class="line"><span class="comment">                                //注册驱动的真正代码</span></span><br><span class="line"><span class="comment">                                DriverManager.registerDriver(new Driver());</span></span><br><span class="line"><span class="comment">                            &#125; catch (SQLException var1) &#123;</span></span><br><span class="line"><span class="comment">                                throw new RuntimeException(&quot;Can&#x27;t register driver!&quot;);</span></span><br><span class="line"><span class="comment">                            &#125;</span></span><br><span class="line"><span class="comment">                        &#125;</span></span><br><span class="line"><span class="comment">                &#125;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">                总结： Class.forName(&quot;com.mysql.jdbc.Driver&quot;);注册一次驱动原因，类com.mysql.jdbc.Driver加载内存中执行静态代码块</span></span><br><span class="line"><span class="comment">                然后注册驱动了</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">             5）为什么 我们自己书写该代码DriverManager.registerDriver(new Driver()); 注册两次？</span></span><br><span class="line"><span class="comment">                因为首先在执行代码：new com.mysql.jdbc.Driver() ，先将类com.mysql.jdbc.Driver加载到内存中执行静态代码块，注册一次驱动，然后</span></span><br><span class="line"><span class="comment">                这里又注册一次</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">              6）从jdbc4开始，我们第一步注册驱动不用我们程序员书写了，，底层在使用到com.mysql.jdbc.Driver类就注册了</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">		Class.forName(&quot;com.mysql.jdbc.Driver&quot;); <span class="operator">/</span><span class="operator">/</span> 后期可以将&quot;com.mysql.jdbc.Driver&quot;字符串写在文件中.</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>演示：<code>Class.forName("包名.类名");</code>会走这个类的静态代码块
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc10.png"></p>
<p>通常开发我们使用Class.forName()
加载驱动。<code>Class.forName("com.mysql.jdbc.Driver");</code>会走Driver类的静态代码块。在静态代码块中注册一次驱动。
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc09.png"></p>
<blockquote>
<p>总结：注册MySQL驱动使用<code>Class.forName("com.mysql.jdbc.Driver");</code></p>
</blockquote>
<h3 id="小结-2">小结</h3>
<ol type="1">
<li><p>导入mysql驱动Jar包</p></li>
<li><p>通过JDBC注册数据库驱动？</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Class.forName(&quot;com.mysql.jdbc.Driver&quot;);</span><br></pre></td></tr></table></figure>
<p>3.从jdk4.0开始，对于我们java开发工程师，第一步不用书写了，底层帮助我们使用com.mysql.jdbc.Driver类先注册驱动了.</p>
<p>驱动必须注册，只是不用我们书写代码注册了。</p></li>
</ol>
<h2 id="获取连接">获取连接</h2>
<p>能够通过JDBC获取数据库连接</p>
<h3 id="讲解-3">讲解</h3>
<h4 id="api介绍-1">API介绍</h4>
<p><code>java.sql.DriverManager</code>类中有如下方法获取数据库连接</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">static</span> Connection <span class="title function_">getConnection</span><span class="params">(String url, String user, String password)</span> </span><br><span class="line">连接到给定数据库 URL ，并返回连接。 </span><br></pre></td></tr></table></figure>
<h4 id="参数说明">参数说明</h4>
<ol type="1">
<li><code>String url</code>：连接数据库的URL，用于说明连接数据库的位置</li>
<li><code>String user</code>：数据库的账号</li>
<li><code>String password</code>：数据库的密码</li>
</ol>
<p>连接数据库的URL地址格式：<code>协议名:子协议://服务器名或IP地址:端口号/数据库名?参数=参数值</code>
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc13.png">
MySQL写法：<code>jdbc:mysql://localhost:3306/day04_db</code>
如果是本地服务器，端口号是默认的3306，则可以简写：<code>jdbc:mysql:///day04_db</code></p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/url.bmp"></p>
<h4 id="注意事项">注意事项</h4>
<blockquote>
<p>如果数据出现乱码需要加上参数:
?useUnicode=true&amp;characterEncoding=utf8，表示让数据库以UTF8编码来处理数据。
如:
jdbc:mysql://localhost:3306/day04_db?useUnicode=true&amp;characterEncoding=utf8</p>
</blockquote>
<h4 id="使用步骤-1">使用步骤</h4>
<p>1.DriverManager.getConnection(url, user, password);
传入对应参数即可</p>
<h4 id="案例代码-1">案例代码</h4>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">Demo01</span> &#123;</span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">		Class.forName(<span class="string">&quot;com.mysql.jdbc.Driver&quot;</span>);</span><br><span class="line">         <span class="comment">/*</span></span><br><span class="line"><span class="comment">            1）使用DriverManager类中的静态方法获取和数据库的连接：</span></span><br><span class="line"><span class="comment">                static Connection getConnection(String url, String user, String password)</span></span><br><span class="line"><span class="comment">                    参数：</span></span><br><span class="line"><span class="comment">                        url：表示建立和数据库服务器连接的地址，书写格式几乎是固定的。</span></span><br><span class="line"><span class="comment">                            jdbc:mysql://连接mysql服务器的ip地址:mysql服务器的端口号/连接的数据库名</span></span><br><span class="line"><span class="comment">                            补充：如果连接的数据库软件在本地，那么url可以简写为：jdbc:mysql:///连接的数据库名</span></span><br><span class="line"><span class="comment">                        user：连接mysql服务器的用户名，这里暂时书写root</span></span><br><span class="line"><span class="comment">                        password：接mysql服务器的密码</span></span><br><span class="line"><span class="comment">                    返回值：Connection，属于java.sql 包下的一个接口，表示连接接口，和数据库的连接。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">		<span class="comment">// 连接到MySQL</span></span><br><span class="line">		<span class="comment">// url: 连接数据库的URL</span></span><br><span class="line">		<span class="comment">// user: 数据库的账号</span></span><br><span class="line">		<span class="comment">// password: 数据库的密码</span></span><br><span class="line">		<span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> DriverManager.getConnection(<span class="string">&quot;jdbc:mysql://localhost:3306/day04_db&quot;</span>, <span class="string">&quot;root&quot;</span>, <span class="string">&quot;1234&quot;</span>);</span><br><span class="line">		System.out.println(conn);</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h4 id="案例效果">案例效果</h4>
<p>1.连接成功 <img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc14.png">
2.连接失败 <img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc15.png"></p>
<h3 id="小结-3">小结</h3>
<ol type="1">
<li><p>JDBC获取数据库连接使用哪个API？</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DriverManager.getConnection(数据库url, 账号, 密码)</span><br></pre></td></tr></table></figure></li>
<li><p>通过JDBC连接mysql的URL写法？</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">jdbc:mysql:<span class="operator">/</span><span class="operator">/</span>服务器ip地址:<span class="number">3306</span><span class="operator">/</span>数据库</span><br></pre></td></tr></table></figure></li>
</ol>
<p>3.如果连接<strong>mysql服务器在本地</strong>，那么url可以简写：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">jdbc:mysql:<span class="operator">/</span><span class="operator">/</span><span class="operator">/</span>数据库</span><br></pre></td></tr></table></figure>
<h2 id="jdbc实现对单表数据增删改">JDBC实现对单表数据增、删、改</h2>
<h3 id="目标-3">目标</h3>
<p>通过JDBC实现对单表数据增、删、改、查</p>
<h3 id="讲解-4">讲解</h3>
<h4 id="jdbc实现对单表数据增删改-1">JDBC实现对单表数据增、删、改</h4>
<p>我们要对数据库进行增、删、改、查，需要使用<code>Statement</code>对象来执行SQL语句。</p>
<h5 id="api介绍-2">API介绍</h5>
<p>获取Statement对象</p>
<p>在<code>java.sql.Connection</code>接口中有如下方法获取到<code>Statement</code>对象</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">Statement <span class="title function_">createStatement</span><span class="params">()</span> </span><br><span class="line">创建一个 Statement 对象来将 SQL 语句发送到数据库</span><br></pre></td></tr></table></figure>
<p>Statement的API介绍</p>
<ol type="1">
<li><p>```java int executeUpdate(String sql)
根据执行的DML（INSERT、UPDATE、DELETE）语句，返回受影响的行数
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">2. ```java</span><br><span class="line">   ResultSet executeQuery(String sql)</span><br><span class="line">   根据查询语句返回结果集,只能执行SELECT语句</span><br></pre></td></tr></table></figure></p>
<blockquote>
<p>注意：在MySQL中，只要不是查询就是修改。 executeUpdate：用于执行增删改
executeQuery：用于执行查询</p>
</blockquote></li>
</ol>
<h4 id="使用步骤-2">使用步骤</h4>
<ol type="1">
<li>注册驱动</li>
<li>获取连接</li>
<li>获取Statement对象</li>
<li>使用Statement对象执行SQL语句</li>
<li>释放资源</li>
</ol>
<h4 id="案例代码-2">案例代码</h4>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">Demo03</span> &#123;</span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">		Class.forName(<span class="string">&quot;com.mysql.jdbc.Driver&quot;</span>);</span><br><span class="line"></span><br><span class="line">		<span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> DriverManager.getConnection(<span class="string">&quot;jdbc:mysql:///day04_db&quot;</span>, <span class="string">&quot;root&quot;</span>, <span class="string">&quot;1234&quot;</span>);</span><br><span class="line">		System.out.println(conn);</span><br><span class="line"></span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            1)获取发送sql语句对象使用Connection连接接口对象调用Connection接口的方法：</span></span><br><span class="line"><span class="comment">                 Statement createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库。</span></span><br><span class="line"><span class="comment">                 PreparedStatement prepareStatement(String sql) 创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库。</span></span><br><span class="line"><span class="comment">             2）Statement和PreparedStatement都可以用来向数据库发送sql语句，Statement是PreparedStatement的父接口。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">             3）PreparedStatement 是安全的，Statement不安全的。并且Statement效率低，PreparedStatement效率高</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">             4）Connection还可以手动控制mysql事务：</span></span><br><span class="line"><span class="comment">                开启事务  void setAutoCommit(boolean autoCommit) 将此连接的自动提交模式设置为给定状态。</span></span><br><span class="line"><span class="comment">                            autoCommit - 为 true 表示启用自动提交模式；为 false 表示禁用自动提交模式</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">               1. conn.setAutoCommit(false);</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">                一切正常提交事务  void commit()</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">               2. conn.commit()</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">                出现异常，回滚事务  void rollback()</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">               3. conn.rollback()</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">		<span class="comment">// 从连接中拿到一个Statement对象</span></span><br><span class="line">		<span class="type">Statement</span> <span class="variable">stmt</span> <span class="operator">=</span> conn.createStatement();</span><br><span class="line"></span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            Statement向数据库发送sql语句，使用Statement中的不同的方法可以向数据库发送不同的sql语句：</span></span><br><span class="line"><span class="comment">                1）DQL查询语句：  ResultSet executeQuery(String sql) 执行给定的 SQL 语句，该语句返回单个 ResultSet 对象。</span></span><br><span class="line"><span class="comment">                                                参数：sql - 要发送给数据库的 SQL 语句，通常为静态 SQL SELECT 语句</span></span><br><span class="line"><span class="comment">                                                 返回值：ResultSet用来存放查询的结果，表示结果集</span></span><br><span class="line"><span class="comment">                2）DML增删改和DDL语句(创建表和数据库)使用的方法：</span></span><br><span class="line"><span class="comment">                     int executeUpdate(String sql)</span></span><br><span class="line"><span class="comment">                            执行给定 SQL 语句，该语句可能为 INSERT、UPDATE 或 DELETE 语句(DML)，</span></span><br><span class="line"><span class="comment">                            或者不返回任何内容的 SQL 语句（如 SQL DDL 语句）。</span></span><br><span class="line"><span class="comment">                          返回值：</span></span><br><span class="line"><span class="comment">                                    1) 对于 SQL 数据操作语言 (DML) 语句，返回行记录数，影响的行数</span></span><br><span class="line"><span class="comment">                                    2) 对于什么都不返回的 SQL 语句，返回 0 ，执行DDL返回的是0 了解</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">		<span class="comment">// 1.插入记录</span></span><br><span class="line">		<span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;insert into user values(null, &#x27;zhaoliu&#x27;, &#x27;abc&#x27;)&quot;</span>;</span><br><span class="line">		<span class="type">int</span> <span class="variable">i</span> <span class="operator">=</span> stmt.executeUpdate(sql);</span><br><span class="line">		System.out.println(<span class="string">&quot;影响的行数:&quot;</span> + i);</span><br><span class="line"></span><br><span class="line">		<span class="comment">// 2.修改记录</span></span><br><span class="line">		sql = <span class="string">&quot;update user set username=&#x27;tianqi&#x27; where username=&#x27;zhaoliu&#x27;&quot;</span>;</span><br><span class="line">		i = stmt.executeUpdate(sql);</span><br><span class="line">		System.out.println(<span class="string">&quot;影响的行数:&quot;</span> + i);</span><br><span class="line"></span><br><span class="line">		<span class="comment">// 3.删除记录</span></span><br><span class="line">		sql = <span class="string">&quot;delete from user where id=4&quot;</span>;</span><br><span class="line">		i = stmt.executeUpdate(sql);</span><br><span class="line">		System.out.println(<span class="string">&quot;影响的行数:&quot;</span> + i);</span><br><span class="line">		</span><br><span class="line">		<span class="comment">// 释放资源</span></span><br><span class="line">		stmt.close();</span><br><span class="line">		conn.close();</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h2 id="jdbc实现对单表数据查询">JDBC实现对单表数据查询</h2>
<h3 id="目标-4">目标</h3>
<p>能够掌握JDBC实现对单表数据查询</p>
<h3 id="讲解-5">讲解</h3>
<p><code>ResultSet</code>用于保存执行查询SQL语句的结果。
我们不能一次性取出所有的数据，需要一行一行的取出。</p>
<h4 id="resultset的原理">ResultSet的原理</h4>
<ol type="1">
<li>ResultSet内部有一个指针,刚开始记录开始位置</li>
<li>调用next方法, ResultSet内部指针会移动到下一行数据</li>
<li>我们可以通过ResultSet得到一行数据 getXxx得到某列数据 <img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc18.png"></li>
</ol>
<h4 id="resultset获取数据的api">ResultSet获取数据的API</h4>
<p>其实ResultSet获取数据的API是有规律的get后面加数据类型。我们统称<code>getXXX()</code>
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc19.png"></p>
<p>​ 例如：</p>
<figure>
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/%E7%BB%93%E6%9E%9C%E9%9B%86.bmp" alt="img">
<figcaption aria-hidden="true">img</figcaption>
</figure>
<p>对于上图中的一行数据，我要获取username为zhangsan这列的值，有如下2种写法：</p>
<ol type="1">
<li><p>rs.getString(“username”); 通过列名获取该列的值。</p></li>
<li><p>rs.getString(2);
通过username列所在的第二个位置获取该列的值。</p></li>
</ol>
<h4 id="使用jdbc查询数据库中的数据的步骤">使用JDBC查询数据库中的数据的步骤</h4>
<ol type="1">
<li>注册驱动</li>
<li>获取连接</li>
<li>获取到Statement</li>
<li>使用Statement执行SQL</li>
<li>ResultSet处理结果</li>
<li>关闭资源</li>
</ol>
<h4 id="案例代码-3">案例代码</h4>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">Demo04</span> &#123;</span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">		Class.forName(<span class="string">&quot;com.mysql.jdbc.Driver&quot;</span>);</span><br><span class="line">		</span><br><span class="line">		<span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> DriverManager.getConnection(<span class="string">&quot;jdbc:mysql:///day04_db&quot;</span>, <span class="string">&quot;root&quot;</span>, <span class="string">&quot;1234&quot;</span>);</span><br><span class="line">		<span class="type">Statement</span> <span class="variable">stmt</span> <span class="operator">=</span> conn.createStatement();</span><br><span class="line">		</span><br><span class="line">		<span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;select * from user&quot;</span>;</span><br><span class="line">		<span class="type">ResultSet</span> <span class="variable">rs</span> <span class="operator">=</span> stmt.executeQuery(sql);</span><br><span class="line">		</span><br><span class="line">		<span class="comment">// 内部有一个指针,只能取指针指向的那条记录</span></span><br><span class="line">        <span class="keyword">while</span>(rs.next())&#123;<span class="comment">// 指针移动一行,有数据才返回true</span></span><br><span class="line">				<span class="type">int</span> <span class="variable">id</span> <span class="operator">=</span> rs.getInt(<span class="string">&quot;id&quot;</span>);</span><br><span class="line">				<span class="type">String</span> <span class="variable">name</span> <span class="operator">=</span> rs.getString(<span class="number">2</span>);</span><br><span class="line">				String pwd= rs.getString(<span class="number">3</span>);</span><br><span class="line">				System.out.println(id+<span class="string">&quot;+++&quot;</span>+name+<span class="string">&quot;++++&quot;</span>+pwd);</span><br><span class="line">			&#125;</span><br><span class="line">		</span><br><span class="line">		<span class="comment">// 关闭资源</span></span><br><span class="line">		rs.close();</span><br><span class="line">		stmt.close();</span><br><span class="line">		conn.close();</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h3 id="小结-4">小结</h3>
<p>其实我们使用JDBC操作数据库的步骤都是固定的。不同的地方是在编写SQL语句</p>
<ol type="1">
<li>注册驱动</li>
<li>获取连接</li>
<li>获取到Statement</li>
<li>使用Statement执行SQL</li>
<li>ResultSet处理结果</li>
<li>关闭资源</li>
</ol>
<p>ResultSet如何获取数据？</p>
<ol type="1">
<li>调用next方法, ResultSet内部指针会移动到下一行数据</li>
<li>我们可以通过ResultSet得到一行数据 getXxx得到某列数据</li>
</ol>
<figure>
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/image-20210720164733101.png" alt="image-20210720164733101">
<figcaption aria-hidden="true">image-20210720164733101</figcaption>
</figure>
<h2 id="根据别名查询处理结果集扩展">根据别名查询处理结果集扩展</h2>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.itheima.sh.a_jdbc_01;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.DriverManager;</span><br><span class="line"><span class="keyword">import</span> java.sql.ResultSet;</span><br><span class="line"><span class="keyword">import</span> java.sql.Statement;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">JDBC03Test</span> &#123;</span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            需求：查询数据表user2所有数据输出到控制台</span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">        <span class="comment">//2.获取连接</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">url</span> <span class="operator">=</span> <span class="string">&quot;jdbc:mysql://localhost:3306/day03_heima138&quot;</span>;<span class="comment">//连接mysql服务器地址</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">username</span> <span class="operator">=</span> <span class="string">&quot;root&quot;</span>;<span class="comment">//连接mysql服务器用户名</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">password</span> <span class="operator">=</span> <span class="string">&quot;1234&quot;</span>;<span class="comment">//连接mysql服务器密码，这里输入你的密码</span></span><br><span class="line">        <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> DriverManager.getConnection(url, username, password);</span><br><span class="line"></span><br><span class="line">        <span class="comment">//3.定义sql</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;select id,username,password pwd from user2&quot;</span>;</span><br><span class="line"></span><br><span class="line">        <span class="comment">//4.获取发送sql语句对象</span></span><br><span class="line">        <span class="type">Statement</span> <span class="variable">st</span> <span class="operator">=</span> conn.createStatement();</span><br><span class="line"></span><br><span class="line">        <span class="comment">//5.发送sql语句</span></span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            1.ResultSet 表示结果集，用来存放查询的结果，因为查询有可能是多行数据，所以使用集合存储</span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">        <span class="type">ResultSet</span> <span class="variable">rs</span> <span class="operator">=</span> st.executeQuery(sql);</span><br><span class="line"></span><br><span class="line">        <span class="comment">//6.处理结果</span></span><br><span class="line">        <span class="keyword">while</span>(rs.next())&#123;</span><br><span class="line">            <span class="comment">//获取数据</span></span><br><span class="line">            <span class="type">int</span> <span class="variable">id</span> <span class="operator">=</span> rs.getInt(<span class="string">&quot;id&quot;</span>);</span><br><span class="line">            <span class="type">String</span> <span class="variable">userName</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;username&quot;</span>);</span><br><span class="line">            <span class="comment">/*</span></span><br><span class="line"><span class="comment">                对应处理结果集ResultSet中的getXxx(xxx 变量名)：</span></span><br><span class="line"><span class="comment">                    1）根据第几列来获取字段值：例如password属于第三列，那么这里获取password值是：rs.getString(3);</span></span><br><span class="line"><span class="comment">                    2）根据字段名或者别名来获取字段值：如果sql语句某个字段有别名，那么只能书写别名，不能书写字段</span></span><br><span class="line"><span class="comment">                        sql语句：select id,username,password pwd from user2</span></span><br><span class="line"><span class="comment">                        获取密码值：rs.getString(&quot;pwd&quot;);</span></span><br><span class="line"><span class="comment">             */</span></span><br><span class="line">            <span class="type">String</span> <span class="variable">pwd</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;pwd&quot;</span>);</span><br><span class="line"></span><br><span class="line">            <span class="comment">//输出</span></span><br><span class="line">            System.out.println(id+<span class="string">&quot;---&quot;</span>+userName+<span class="string">&quot;---&quot;</span>+pwd);</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="comment">//7.释放资源</span></span><br><span class="line">        rs.close();</span><br><span class="line">        st.close();</span><br><span class="line">        conn.close();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h2 id="jdbc实现登录案例">JDBC实现登录案例</h2>
<h3 id="目标-5">目标</h3>
<p>模拟用户输入账号和密码登录网站</p>
<ol type="1">
<li>输入正确的账号，密码，显示登录成功</li>
<li>输入错误的账号，密码，显示登录失败</li>
</ol>
<h3 id="讲解-6">讲解</h3>
<h4 id="案例分析">案例分析</h4>
<ol type="1">
<li>使用数据库保存用户的账号和密码</li>
<li>使用SQL根据用户的账号和密码去数据库查询数据</li>
<li>如果查询到数据，说明登录成功</li>
<li>如果查询不到数据，说明登录失败</li>
</ol>
<h4 id="实现步骤">实现步骤</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 创建数据库</span></span><br><span class="line"><span class="keyword">create</span> database day04_db;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 切换数据库</span></span><br><span class="line">use day04_db;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 用户表</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="keyword">user</span> (</span><br><span class="line">  id <span class="type">int</span> <span class="keyword">primary</span> key auto_increment,</span><br><span class="line">  username <span class="type">varchar</span>(<span class="number">30</span>) <span class="keyword">unique</span> <span class="keyword">not</span> <span class="keyword">null</span>,</span><br><span class="line">  password <span class="type">varchar</span>(<span class="number">30</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">user</span>(username, password) <span class="keyword">values</span>(<span class="string">&#x27;zhangsan&#x27;</span>,<span class="string">&#x27;123&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">user</span>(username, password) <span class="keyword">values</span>(<span class="string">&#x27;lisi&#x27;</span>,<span class="string">&#x27;123&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">user</span>(username, password) <span class="keyword">values</span>(<span class="string">&#x27;wangwu&#x27;</span>,<span class="string">&#x27;123&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>
<p>1.使用SQL根据用户的账号和密码去数据库查询数据.</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.itheima.sh.a_jdbc_01;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.DriverManager;</span><br><span class="line"><span class="keyword">import</span> java.sql.ResultSet;</span><br><span class="line"><span class="keyword">import</span> java.sql.Statement;</span><br><span class="line"><span class="keyword">import</span> java.util.Scanner;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">JDBCTest04</span> &#123;</span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            练习：模拟登录，如果登录成功，提示登录成功，登录失败，提示失败</span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">        <span class="comment">//1.创建键盘录入对象</span></span><br><span class="line">        <span class="type">Scanner</span> <span class="variable">sc</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Scanner</span>(System.in);</span><br><span class="line">        <span class="comment">//2.提示输入用户名和密码</span></span><br><span class="line">        System.out.println(<span class="string">&quot;-------请输入用户名:-------&quot;</span>);</span><br><span class="line">        <span class="comment">//获取用户名</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">inputUsername</span> <span class="operator">=</span> sc.nextLine();</span><br><span class="line"></span><br><span class="line">        System.out.println(<span class="string">&quot;-------请输入密码:-------&quot;</span>);</span><br><span class="line">        <span class="comment">//获取密码</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">inputPassword</span> <span class="operator">=</span> sc.nextLine();</span><br><span class="line">        <span class="comment">//3.获取数据</span></span><br><span class="line">        <span class="comment">//4.注册驱动</span></span><br><span class="line">        <span class="comment">//5.获取和数据库连接对象</span></span><br><span class="line">        <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> DriverManager.getConnection(<span class="string">&quot;jdbc:mysql://localhost:3306/day03_heima139&quot;</span>, <span class="string">&quot;root&quot;</span>, <span class="string">&quot;1234&quot;</span>);</span><br><span class="line">        <span class="comment">//6.获取发送sql语句的对象</span></span><br><span class="line">        <span class="type">Statement</span> <span class="variable">st</span> <span class="operator">=</span> conn.createStatement();</span><br><span class="line">        <span class="comment">//7.发送sql语句</span></span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            错误的完整的sql语句：select * from user3 where username=zhangsanand password=123</span></span><br><span class="line"><span class="comment">            正确的完整的sql语句：select * from user3 where username=&#x27;zhangsan&#x27; and password=&#x27;123&#x27;</span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">        <span class="type">ResultSet</span> <span class="variable">rs</span> <span class="operator">=</span> st.executeQuery(<span class="string">&quot;select * from user3 where username=&#x27;&quot;</span> + inputUsername + <span class="string">&quot;&#x27; and password=&#x27;&quot;</span> + inputPassword+<span class="string">&quot;&#x27;&quot;</span>);</span><br><span class="line">        <span class="comment">//8.处理结果集</span></span><br><span class="line">        <span class="comment">//用户名唯一，查询的是一条数据，所以这里使用if即可</span></span><br><span class="line">        <span class="keyword">if</span>(rs.next())&#123;</span><br><span class="line">            <span class="comment">//rs.next() :如果当前指针指向的行有数据则返回true</span></span><br><span class="line">            <span class="comment">//获取用户名</span></span><br><span class="line">            <span class="type">String</span> <span class="variable">username</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;username&quot;</span>);</span><br><span class="line">            <span class="comment">//输出</span></span><br><span class="line">            System.out.println(<span class="string">&quot;恭喜您，亲，登录成功，欢迎光临我的小店，你的用户名是&quot;</span>+username);</span><br><span class="line">        &#125;<span class="keyword">else</span>&#123;</span><br><span class="line">            System.out.println(<span class="string">&quot;用户名或者密码错误&quot;</span>);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//9.释放资源</span></span><br><span class="line">        rs.close();</span><br><span class="line">        st.close();</span><br><span class="line">        conn.close();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="小结-5">小结</h3>
<p>登录案例步骤</p>
<ol type="1">
<li>使用数据库保存用户的账号和密码</li>
<li>让用户输入账号和密码</li>
<li>使用SQL根据用户的账号和密码去数据库查询数据</li>
<li>如果查询到数据，说明登录成功</li>
<li>如果查询不到数据，说明登录失败</li>
</ol>
<h2 id="preparedsatement预编译对象">PreparedSatement预编译对象</h2>
<h3 id="目标-6">目标</h3>
<p>能够理解什么是SQL注入</p>
<p>能够理解PreparedSatement的执行原理</p>
<h3 id="讲解-7">讲解</h3>
<h4 id="sql注入问题">SQL注入问题</h4>
<p>sql注入：由于没有对用户输入进行充分检查，而SQL又是拼接而成，在用户输入参数时，在参数中添加一些SQL
关键字，达到改变SQL运行结果的目的，也可以完成恶意攻击。</p>
<p>简单来说就是：用户在页面提交数据的时候人为的添加一些特殊字符，使得sql语句的结构发生了变化，最终可以在没有用户名或者密码的情况下进行登录。</p>
<p>案例：模拟登陆。</p>
<h4 id="sql注入原因演示_模拟登陆">sql注入原因演示_模拟登陆：</h4>
<p>sql注入代码演示：</p>
<p>需求: 根据用户名和密码 查询用户信息(只知道用户名,不知道密码)。</p>
<p>恶意注入方式：在sql语句中添加 -- 是mysql的注释。</p>
<p>用户名username输入 zhangsan' 空格--空格 ，密码password 随意。</p>
<p>select * from user where username ='zhangsan' -- ' and password
='kajajha''' ;</p>
<p>对上述sql语句进行说明：</p>
<p>-- ' and password ='kajajha''' ; --
表示注释的意思，这样就会将密码都给注释掉了，就相当于只根据用户名zhangsan来查询了。</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/sql%E6%B3%A8%E5%85%A5%E9%97%AE%E9%A2%98.bmp"></p>
<p>注意：以上的 zhangsan' 空格--空格
中的用户名zhangsan是数据库存在的。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.itheima.sh.a_jdbc_01;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.DriverManager;</span><br><span class="line"><span class="keyword">import</span> java.sql.ResultSet;</span><br><span class="line"><span class="keyword">import</span> java.sql.Statement;</span><br><span class="line"><span class="keyword">import</span> java.util.Scanner;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">JDBCTest05</span> &#123;</span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            sql注入问题：在对于sql语句使用变量的部分输入一些特殊的符号导致输入的数据不正确也可以完成查询数据</span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">        <span class="comment">//1.创建键盘录入对象</span></span><br><span class="line">        <span class="type">Scanner</span> <span class="variable">sc</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Scanner</span>(System.in);</span><br><span class="line">        <span class="comment">//2.提示输入用户名和密码</span></span><br><span class="line">        System.out.println(<span class="string">&quot;-------请输入用户名:-------&quot;</span>);</span><br><span class="line">        <span class="comment">//获取用户名</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">inputUsername</span> <span class="operator">=</span> sc.nextLine();</span><br><span class="line"></span><br><span class="line">        System.out.println(<span class="string">&quot;-------请输入密码:-------&quot;</span>);</span><br><span class="line">        <span class="comment">//获取密码</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">inputPassword</span> <span class="operator">=</span> sc.nextLine();</span><br><span class="line">        <span class="comment">//3.获取数据</span></span><br><span class="line">        <span class="comment">//4.注册驱动</span></span><br><span class="line">        <span class="comment">//5.获取和数据库连接对象</span></span><br><span class="line">        <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> DriverManager.getConnection(<span class="string">&quot;jdbc:mysql://localhost:3306/day03_heima139&quot;</span>, <span class="string">&quot;root&quot;</span>, <span class="string">&quot;1234&quot;</span>);</span><br><span class="line">        <span class="comment">//6.获取发送sql语句的对象</span></span><br><span class="line">        <span class="type">Statement</span> <span class="variable">st</span> <span class="operator">=</span> conn.createStatement();</span><br><span class="line">        <span class="comment">//7.发送sql语句</span></span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            sql注入：</span></span><br><span class="line"><span class="comment">            正确的完整的sql语句：select * from user3 where username=&#x27;zhangsan&#x27; -- &#x27; and password=&#x27;i182928&#x27;</span></span><br><span class="line"><span class="comment">            说明：上述sql语句在用户输入一些特殊符号(单引号 空格 --等)导致 -- 后面额内容被注释了，结果是之后执行sql:select * from user3 where username=&#x27;zhangsan&#x27;</span></span><br><span class="line"><span class="comment">            其实就是根据用户名查询数据了，即使输入密码，由于被注释了也不会根据密码查询了</span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">        <span class="type">ResultSet</span> <span class="variable">rs</span> <span class="operator">=</span> st.executeQuery(<span class="string">&quot;select * from user3 where username=&#x27;&quot;</span> + inputUsername + <span class="string">&quot;&#x27; and password=&#x27;&quot;</span> + inputPassword+<span class="string">&quot;&#x27;&quot;</span>);</span><br><span class="line">        <span class="comment">//8.处理结果集</span></span><br><span class="line">        <span class="comment">//用户名唯一，查询的是一条数据，所以这里使用if即可</span></span><br><span class="line">        <span class="keyword">if</span>(rs.next())&#123;</span><br><span class="line">            <span class="comment">//rs.next() :如果当前指针指向的行有数据则返回true</span></span><br><span class="line">            <span class="comment">//获取用户名</span></span><br><span class="line">            <span class="type">String</span> <span class="variable">username</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;username&quot;</span>);</span><br><span class="line">            <span class="comment">//输出</span></span><br><span class="line">            System.out.println(<span class="string">&quot;恭喜您，亲，登录成功，欢迎光临我的小店，你的用户名是&quot;</span>+username);</span><br><span class="line">        &#125;<span class="keyword">else</span>&#123;</span><br><span class="line">            System.out.println(<span class="string">&quot;用户名或者密码错误&quot;</span>);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//9.释放资源</span></span><br><span class="line">        rs.close();</span><br><span class="line">        st.close();</span><br><span class="line">        conn.close();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="小结-6">小结</h3>
<p><strong>问题根本原因：</strong></p>
<p>之所以有sql注入的问题，无非是在参数中设置了一些特殊字符，使sql语句在拼接这些参数的时候因为特殊字符的原因改变了sql语句原来的规则。</p>
<p><strong>问题的解决方案：</strong></p>
<p>使用PreparedStatement 解决SQL注入问题，运行在SQL中参数以 ?
占位符的方式表示。</p>
<h2 id="preparedstatement解决sql注入方案">PreparedStatement解决SQL注入方案</h2>
<h3 id="目标-7">目标</h3>
<p>掌握PreparedStatement是如何解决SQL注入问题的</p>
<h3 id="讲解-8">讲解</h3>
<p>1.获取PreparedStatement对象</p>
<p>PreparedStatement是Statement的子接口，可以防止sql注入问题。可以通过Connection接口中的prepareStatement(sql)方法获得PreparedStatement的对象。</p>
<p>方法如下所示：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"> PreparedStatement	<span class="title function_">prepareStatement</span><span class="params">(String sql)</span>  创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库。</span><br></pre></td></tr></table></figure>
<p>注意：sql提前创建好的。sql语句中需要参数。使用？进行占位。</p>
<p>举例：</p>
<p>select *from user where username='zhangsan' and password =
'123456';</p>
<p>使用？进行占位</p>
<p>select *from user where username=? and password = ?;</p>
<p>String sql=”select *from user where username=? and password = ?”;</p>
<p>步骤一：<strong>PreparedStatement pstmt =
conn.prepareStatement(sql);</strong>
-----需要你事先传递sql。如果sql需要参数，使用？进行占位。</p>
<p>步骤二：设置参数（执行sql之前）：pstmt.setXXX(int index, 要放入的值)
-----根据不同类型的数据进行方法的选择。第一个参数index表示的是？出现的位置。从1开始计数，有几个问号，就需要传递几个参数。</p>
<p>方法的参数说明：</p>
<p>第一个参数：int index ;表示的是问号出现的位置。 问号是从1开始计数</p>
<p>第二个参数：给问号的位置传入的值。</p>
<p>步骤三、执行，不需要在传递sql了。</p>
<p>pstmt.executeQuery();---执行select</p>
<p>pstmt.executeUpdate();---执行insert，delete，update</p>
<p>小结：</p>
<p>1.使用预编译接口PreparedStatement 好处：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1.</span>解决sql注入问题</span><br><span class="line"><span class="number">2.</span>提供效率，对sql语句只会预编译一次</span><br><span class="line">    </span><br></pre></td></tr></table></figure>
<p>2.使用编译接口PreparedStatement步骤：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1</span>)使用连接对象调用方法获取预编译接口对象：<span class="type">PreparedStatement</span>  <span class="variable">pstmt</span> <span class="operator">=</span>  conn.prepareStatement(sql); </span><br><span class="line"><span class="number">2</span>）给sql语句占位符赋值：pstmt.setXxx(第几个占位符,实际值)</span><br><span class="line"><span class="number">3</span>）运行sql语句：</span><br><span class="line">    	 pstmt.executeQuery();---执行select</span><br><span class="line">   		 pstmt.executeUpdate();---执行insert，delete，update</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h2 id="preparedstatement的-应用掌握">PreparedStatement的
应用(掌握)</h2>
<h3 id="目标-8">目标</h3>
<p>能够掌握PreparedSatement实现查询和添加数据</p>
<h3 id="讲解-9">讲解</h3>
<p><strong>1、需求: 根据用户名和密码查询用户信息。</strong></p>
<p>代码如下所示：</p>
<p>说明：导包必须都得使用java.sql包下的。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.itheima.sh.a_jdbc_01;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.*;</span><br><span class="line"><span class="keyword">import</span> java.util.Scanner;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">JDBCTest06</span> &#123;</span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            使用PreparedStatement接口解决sql注入问题</span></span><br><span class="line"><span class="comment">                1.使用Connection接口对象调用方法，获取PreparedStatement预编译接口</span></span><br><span class="line"><span class="comment">                    PreparedStatement prepareStatement(String sql)</span></span><br><span class="line"><span class="comment">                2.使用预编译接口  PreparedStatement的对象调用PreparedStatement接口中的方法给sql语句中的占位符赋值</span></span><br><span class="line"><span class="comment">                    setXxx(第几个占位符,实际值)</span></span><br><span class="line"><span class="comment">                3.使用预编译接口  PreparedStatement对象调用PreparedStatement接口中的方法执行sql</span></span><br><span class="line"><span class="comment">                     ResultSet executeQuery()  执行DQL(查询语句)</span></span><br><span class="line"><span class="comment">                      int executeUpdate()    执行DML(增删改语句)</span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">        <span class="comment">//1.创建键盘录入对象</span></span><br><span class="line">        <span class="type">Scanner</span> <span class="variable">sc</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Scanner</span>(System.in);</span><br><span class="line">        <span class="comment">//2.提示输入用户名和密码</span></span><br><span class="line">        System.out.println(<span class="string">&quot;-------请输入用户名:-------&quot;</span>);</span><br><span class="line">        <span class="comment">//获取用户名</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">inputUsername</span> <span class="operator">=</span> sc.nextLine();</span><br><span class="line"></span><br><span class="line">        System.out.println(<span class="string">&quot;-------请输入密码:-------&quot;</span>);</span><br><span class="line">        <span class="comment">//获取密码</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">inputPassword</span> <span class="operator">=</span> sc.nextLine();</span><br><span class="line">        <span class="comment">//3.获取数据</span></span><br><span class="line">        <span class="comment">//4.注册驱动</span></span><br><span class="line">        <span class="comment">//5.获取和数据库连接对象</span></span><br><span class="line">        <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> DriverManager.getConnection(<span class="string">&quot;jdbc:mysql://localhost:3306/day03_heima139&quot;</span>, <span class="string">&quot;root&quot;</span>, <span class="string">&quot;1234&quot;</span>);</span><br><span class="line">        <span class="comment">//6.使用Connection接口对象调用方法，获取PreparedStatement预编译接口</span></span><br><span class="line">        <span class="comment">// PreparedStatement prepareStatement(String sql)</span></span><br><span class="line">        <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;select * from user3 where username=? and password=?&quot;</span>;</span><br><span class="line">        <span class="type">PreparedStatement</span> <span class="variable">pst</span> <span class="operator">=</span> conn.prepareStatement(sql);</span><br><span class="line"></span><br><span class="line">        <span class="comment">//2.使用预编译接口  PreparedStatement的对象调用PreparedStatement接口中的方法给sql语句中的占位符赋值</span></span><br><span class="line">        <span class="comment">//setXxx(第几个占位符,实际值)</span></span><br><span class="line">        <span class="comment">//第一个参数1表示上述sql语句中的第一个占位符(?)位置</span></span><br><span class="line">        <span class="comment">//inputUsername :表示给第一个占位符赋的实际值</span></span><br><span class="line">        <span class="comment">// zhangsan\&#x27;\ \-\-</span></span><br><span class="line">        pst.setString(<span class="number">1</span>,inputUsername);</span><br><span class="line"></span><br><span class="line">        <span class="comment">//第一个参数2表示上述sql语句中的第二个占位符(?)位置</span></span><br><span class="line">        <span class="comment">//inputPassword :表示给第二个占位符赋的实际值</span></span><br><span class="line">        pst.setString(<span class="number">2</span>,inputPassword);</span><br><span class="line"></span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">             3.使用预编译接口  PreparedStatement对象调用PreparedStatement接口中的方法执行sql</span></span><br><span class="line"><span class="comment">                     ResultSet executeQuery()  执行DQL(查询语句)</span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">        <span class="type">ResultSet</span> <span class="variable">rs</span> <span class="operator">=</span> pst.executeQuery();</span><br><span class="line"></span><br><span class="line">        <span class="comment">//8.处理结果集</span></span><br><span class="line">        <span class="comment">//用户名唯一，查询的是一条数据，所以这里使用if即可</span></span><br><span class="line">        <span class="keyword">if</span>(rs.next())&#123;</span><br><span class="line">            <span class="comment">//rs.next() :如果当前指针指向的行有数据则返回true</span></span><br><span class="line">            <span class="comment">//获取用户名</span></span><br><span class="line">            <span class="type">String</span> <span class="variable">username</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;username&quot;</span>);</span><br><span class="line">            <span class="comment">//输出</span></span><br><span class="line">            System.out.println(<span class="string">&quot;恭喜您，亲，登录成功，欢迎光临我的小店，你的用户名是&quot;</span>+username);</span><br><span class="line">        &#125;<span class="keyword">else</span>&#123;</span><br><span class="line">            System.out.println(<span class="string">&quot;用户名或者密码错误&quot;</span>);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//9.释放资源</span></span><br><span class="line">        rs.close();</span><br><span class="line">        pst.close();</span><br><span class="line">        conn.close();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>小结：</p>
<p>上述如何解决sql注入的问题呢？</p>
<p>在方法setXxx()内部解决的。例如上述 st.setString(1,inputUsername);
，将输入的用户名 "zhangsan' --
"传入给setString方法体中，在该方法体中使用转义符号 /
，将特殊符号给转义了，转义之后再发送给mysql服务器，那么特殊符号例如--就不是注释的意思就是普通字符，mysql会认为用户名的值是：zhangsan'
--</p>
<p><strong>2、 需求：使用预编译对象执行删除操作</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.itheima.sh.a_jdbc_01;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.DriverManager;</span><br><span class="line"><span class="keyword">import</span> java.sql.PreparedStatement;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">JDBCTest07</span> &#123;</span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">        <span class="comment">/*</span></span><br><span class="line"><span class="comment">            需求：使用预编译对象执行删除操作</span></span><br><span class="line"><span class="comment">         */</span></span><br><span class="line">        <span class="comment">//1.注册驱动</span></span><br><span class="line">        <span class="comment">//2.获取连接</span></span><br><span class="line">        <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> DriverManager.getConnection(<span class="string">&quot;jdbc:mysql://localhost:3306/day03_heima139&quot;</span>, <span class="string">&quot;root&quot;</span>, <span class="string">&quot;1234&quot;</span>);</span><br><span class="line">        <span class="comment">//3.获取发送sql语句的预编译对象</span></span><br><span class="line">        <span class="type">PreparedStatement</span> <span class="variable">pst</span> <span class="operator">=</span> conn.prepareStatement(<span class="string">&quot;delete from user3 where id=?&quot;</span>);</span><br><span class="line">        <span class="comment">//4.发送sql语句</span></span><br><span class="line">        <span class="comment">//给占位符赋值</span></span><br><span class="line">        pst.setInt(<span class="number">1</span>, <span class="number">4</span>);<span class="comment">//第一个参数1表示上述sql语句的占位符位置是第一个，第二个参数4表示给id的实际值</span></span><br><span class="line">        <span class="type">int</span> <span class="variable">i</span> <span class="operator">=</span> pst.executeUpdate();</span><br><span class="line">        <span class="comment">//5.处理结果集</span></span><br><span class="line">        System.out.println(<span class="string">&quot;i = &quot;</span> + i);</span><br><span class="line">        <span class="comment">//6.释放资源</span></span><br><span class="line">        pst.close();</span><br><span class="line">        conn.close();</span><br><span class="line"></span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h2 id="执行dql封装成集合的操作"><font color="red">执行DQL封装成集合的操作</font></h2>
<p>需求：将user中的数据查询封装到实体类User对象中，然后将User对象放到list集合中，最后遍历list集合</p>
<p>在实际开发中我们使用三层架构进行开发，层与层之间会将传递的参数封装成实体对象，那么接下来我们来看一下dao(数据处理层)层是如何将查询出的结果封装成user对象的：</p>
<p>说明：封装到User类的对象中是因为我们要把查询的结果进行显示和其他的处理。</p>
<p>举例：</p>
<p>用户想在浏览器查询自己的个人信息，那么我们需要到数据库查询出来，然后把个人的全部信息封装到User类的对象中。然后在将User类的对象传递给前台，最后经过相关技术显示到页面浏览器中，达到显示个人信息的效果。</p>
<p>其实我们生活中到淘宝网站购买商品，查看某个商品信息也是这样做的，将数据库中有关商品信息都先全部封装到Product类的对象中。最后显示到页面中。</p>
<p>按照如下操作，书写代码：</p>
<p>新建一个User类，具体属性如下所示：</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/15.bmp"></p>
<p>在DAO层使用JDBC将查询的数据封装到User类的对象中的代码，如下所示：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Test</span></span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">show</span><span class="params">()</span> &#123;</span><br><span class="line">		<span class="comment">// 初始化值</span></span><br><span class="line">		<span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">		<span class="type">ResultSet</span> <span class="variable">rs</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">		<span class="type">PreparedStatement</span> <span class="variable">pst</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">		<span class="keyword">try</span> &#123;</span><br><span class="line">			conn = JDBCUtils.getConnection();</span><br><span class="line">			<span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;select * from user&quot;</span>;</span><br><span class="line">			<span class="comment">// 获取发送sql的对象</span></span><br><span class="line">			pst = conn.prepareStatement(sql);</span><br><span class="line">			<span class="comment">// 执行sql</span></span><br><span class="line">			rs = pst.executeQuery();</span><br><span class="line">			<span class="comment">//定义集合将遍历结果集封装到List集合中</span></span><br><span class="line">			List&lt;User&gt; list=<span class="keyword">new</span> <span class="title class_">ArrayList</span>&lt;User&gt;();</span><br><span class="line">			<span class="comment">// 处理结果</span></span><br><span class="line">			<span class="keyword">while</span> (rs.next()) &#123;</span><br><span class="line">				<span class="comment">//由于数据库中有多行，所以需要多个User类的对象</span></span><br><span class="line">				<span class="type">User</span> <span class="variable">u</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">User</span>();</span><br><span class="line">				u.setId(rs.getInt(<span class="string">&quot;id&quot;</span>));</span><br><span class="line">				u.setName(rs.getString(<span class="string">&quot;name&quot;</span>));</span><br><span class="line">				u.setCity(rs.getString(<span class="string">&quot;city&quot;</span>));</span><br><span class="line">				<span class="comment">// 将对象添加到集合中</span></span><br><span class="line">				list.add(u);</span><br><span class="line">			&#125;</span><br><span class="line">			System.out.println(list.size());<span class="comment">//3</span></span><br><span class="line">		&#125; <span class="keyword">catch</span> (Exception e) &#123;</span><br><span class="line">			e.printStackTrace();</span><br><span class="line">			</span><br><span class="line">		&#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">			JDBCUtils.release(rs, pst, conn);<span class="comment">// 不要关闭连接，每次使用完之后，把连接还给连接池</span></span><br><span class="line">		&#125;</span><br><span class="line">	&#125;</span><br></pre></td></tr></table></figure>
<p>说明：</p>
<p>上述将从数据库中查询的数据封装到User类的对象中，相对来说比较复杂。我们这里用户属性相对比较少，如果属性多的话会更加复杂。所以，我们完全可以使用更为简单的方式来对数据进行封装到User类的对象中。可以使用后面学习的mybatis进行封装。</p>
<h2 id="jdbc事务">JDBC事务</h2>
<h3 id="目标-9">目标</h3>
<p>能够掌握JDBC操作事务</p>
<h3 id="讲解-10">讲解</h3>
<p>之前我们是使用MySQL的命令来操作事务。接下来我们使用JDBC来操作银行转账的事务。</p>
<h4 id="准备数据">准备数据</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"># 创建一个表：账户表.</span><br><span class="line"><span class="keyword">create</span> database day05_db;</span><br><span class="line"># 使用数据库</span><br><span class="line">use day05_db;</span><br><span class="line"># 创建账号表</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> account(</span><br><span class="line">	id <span class="type">int</span> <span class="keyword">primary</span> key auto_increment,</span><br><span class="line">	name <span class="type">varchar</span>(<span class="number">20</span>),</span><br><span class="line">	money <span class="keyword">double</span></span><br><span class="line">);</span><br><span class="line"># 初始化数据</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> account <span class="keyword">values</span> (<span class="keyword">null</span>,<span class="string">&#x27;a&#x27;</span>,<span class="number">1000</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> account <span class="keyword">values</span> (<span class="keyword">null</span>,<span class="string">&#x27;b&#x27;</span>,<span class="number">1000</span>);</span><br></pre></td></tr></table></figure>
<h4 id="jdbc操作事务">JDBC操作事务</h4>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/JDBC%E6%8E%A7%E5%88%B6%E4%BA%8B%E5%8A%A1.bmp"></p>
<h4 id="api介绍-3">API介绍</h4>
<p><code>Connection</code>接口中与事务有关的方法</p>
<ol type="1">
<li><p>```java void setAutoCommit(boolean autoCommit) throws
SQLException; false：开启事务， true：关闭事务 <figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">2. ```java</span><br><span class="line">   void commit() throws SQLException;</span><br><span class="line">   提交事务</span><br></pre></td></tr></table></figure></p></li>
<li><p>```java void rollback() throws SQLException; 回滚事务
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">说明：</span><br><span class="line"></span><br><span class="line">注意：在jdbc事务操作中，事务的控制都是通过Connection对象完成的，当一个完整的业务操作前，我们首先使用conn.setAutoCommit(false)来开启事务。默认情况下是true的，表示关闭事务，那么一条sql语句就是一个事务，默认提交事务。如果设置为false，那么表示开启事务，所有的sql语句就会都在一个事务中。</span><br><span class="line"></span><br><span class="line">当业务操作完成之后，如果整个操作没有问题，我们需要使用conn.commit()来提交事务。当然了，如果出现了异常，我们需要使用conn.rollback()撤销所有的操作，所以出现异常，需要进行事务的回滚。</span><br><span class="line"></span><br><span class="line">#### 使用步骤</span><br><span class="line"></span><br><span class="line">1. 注册驱动</span><br><span class="line">2. 获取连接</span><br><span class="line">3. **开启事务**</span><br><span class="line">4. 获取到Statement</span><br><span class="line">5. 使用Statement执行SQL</span><br><span class="line">6. **提交或回滚事务**</span><br><span class="line">7. 关闭资源</span><br><span class="line"></span><br><span class="line">#### 案例代码</span><br><span class="line"></span><br><span class="line">如下是使用jdbc操作事务的转账案例代码。</span><br><span class="line"></span><br><span class="line">需求：a转给b 100元。</span><br><span class="line"></span><br><span class="line">分析：</span><br><span class="line"></span><br><span class="line">a用户 money=money-100</span><br><span class="line"></span><br><span class="line">b用户 money=money+100</span><br><span class="line"></span><br><span class="line">```java</span><br><span class="line">package com.itheima.sh.a_jdbc_01;</span><br><span class="line"></span><br><span class="line">import java.sql.Connection;</span><br><span class="line">import java.sql.DriverManager;</span><br><span class="line">import java.sql.PreparedStatement;</span><br><span class="line">import java.sql.SQLException;</span><br><span class="line"></span><br><span class="line">public class JDBCTest08 &#123;</span><br><span class="line">    public static void main(String[] args) &#123;</span><br><span class="line">        Connection conn = null;</span><br><span class="line">        PreparedStatement pst1 = null;</span><br><span class="line">        PreparedStatement pst2 = null;</span><br><span class="line">        //生成try-catch-finally代码块快捷键：ctrl+alt+T,不要选中释放资源的代码</span><br><span class="line">        try &#123;</span><br><span class="line">        /*</span><br><span class="line">            需求：使用jdbc控制mysql事务。</span><br><span class="line">            使用Connection接口中的方法：</span><br><span class="line">                1.开启手动控制事务：void setAutoCommit(false)</span><br><span class="line">                2.一切正常，提交事务：void commit()</span><br><span class="line">                3.出现异常，回滚事务： void rollback()</span><br><span class="line">         */</span><br><span class="line">            //使用事务完成a给b转账100元</span><br><span class="line">            //a-100</span><br><span class="line">            //b+100</span><br><span class="line">            //1.注册驱动</span><br><span class="line">//        DriverManager.registerDriver(new Driver());</span><br><span class="line">            //2.获取数据库连接</span><br><span class="line">            conn = DriverManager.getConnection(&quot;jdbc:mysql://localhost:3306/day03_heima139&quot;, &quot;root&quot;, &quot;1234&quot;);</span><br><span class="line">            //开启手动控制事务 == 1.开启手动控制事务：void setAutoCommit(false)</span><br><span class="line">            conn.setAutoCommit(false);</span><br><span class="line">            //3.获取发送sql语句的预编译对象</span><br><span class="line">            // a-100</span><br><span class="line">            pst1 = conn.prepareStatement(&quot;update account set money=money-100 where name=?&quot;);</span><br><span class="line"></span><br><span class="line">            // b+100</span><br><span class="line">            pst2 = conn.prepareStatement(&quot;update account set money=money+100 where name=?&quot;);</span><br><span class="line">            //4.发送sql语句</span><br><span class="line">            //给占位符赋值</span><br><span class="line">            pst1.setString(1, &quot;a&quot;);</span><br><span class="line">            pst2.setString(1, &quot;b&quot;);</span><br><span class="line"></span><br><span class="line">            //5.发送sql语句</span><br><span class="line">            pst1.executeUpdate();</span><br><span class="line"></span><br><span class="line">            //模拟异常</span><br><span class="line"></span><br><span class="line">//            int i = 1 / 0;</span><br><span class="line"></span><br><span class="line">            pst2.executeUpdate();</span><br><span class="line"></span><br><span class="line">            //2.一切正常，提交事务：void commit()</span><br><span class="line">            conn.commit();</span><br><span class="line"></span><br><span class="line">        &#125; catch (Exception throwables) &#123;</span><br><span class="line">            throwables.printStackTrace();</span><br><span class="line">            try &#123;</span><br><span class="line">                //3.出现异常，回滚事务： void rollback()</span><br><span class="line">                if (conn != null) &#123;</span><br><span class="line">                    conn.rollback();</span><br><span class="line">                &#125;</span><br><span class="line">            &#125; catch (SQLException e) &#123;</span><br><span class="line">                e.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line"></span><br><span class="line">        &#125; finally &#123;</span><br><span class="line">            //5.处理结果</span><br><span class="line">            //6.释放资源</span><br><span class="line">            try &#123;</span><br><span class="line">                if (pst2 != null) &#123;//防止空指针异常</span><br><span class="line">                    pst2.close();</span><br><span class="line">                &#125;</span><br><span class="line">            &#125; catch (SQLException throwables) &#123;</span><br><span class="line">                throwables.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line"></span><br><span class="line">            try &#123;</span><br><span class="line">                if (pst1 != null) &#123;//防止空指针异常</span><br><span class="line">                    pst1.close();</span><br><span class="line">                &#125;</span><br><span class="line">            &#125; catch (SQLException throwables) &#123;</span><br><span class="line">                throwables.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">            try &#123;</span><br><span class="line">                if (conn != null) &#123;//防止空指针异常</span><br><span class="line">                    conn.close();</span><br><span class="line">                &#125;</span><br><span class="line">            &#125; catch (SQLException throwables) &#123;</span><br><span class="line">                throwables.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure></p></li>
</ol>
<h3 id="小结-7">小结</h3>
<p>JDBC中与事务相关的API？<code>Connection</code>接口中setAutoCommit，commit，rollback</p>
<p>JDBC操作事务的步骤？</p>
<ol type="1">
<li>注册驱动</li>
<li>获取连接</li>
<li>获取到Statement</li>
<li><strong>开启事务</strong></li>
<li>使用Statement执行SQL</li>
<li><strong>提交或回滚事务</strong></li>
<li>关闭资源</li>
</ol>
<h2 id="jdbc工具类">JDBC工具类</h2>
<p>工具类：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> ethanliu.jdbc.utils;</span><br><span class="line"></span><br><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Author</span> EthanLiu 16693226842@163.com</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Date</span> 2024/5/5 12:03</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Project</span> MySQL</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Theme</span> JDBC工具类</span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.*;</span><br><span class="line"></span><br><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment"> * 构造方法私有化</span></span><br><span class="line"><span class="comment"> * 提供静态方法</span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">JdbcUtils</span> &#123;</span><br><span class="line">  </span><br><span class="line">    <span class="keyword">private</span> <span class="title function_">JdbcUtils</span><span class="params">()</span> &#123;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="keyword">final</span> <span class="type">String</span> <span class="variable">DRIVER</span> <span class="operator">=</span> <span class="string">&quot;com.mysql.cj.jdbc.Driver&quot;</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="keyword">final</span> <span class="type">String</span> <span class="variable">URL</span> <span class="operator">=</span> <span class="string">&quot;jdbc:mysql://127.0.0.1:3306/Server&quot;</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="keyword">final</span> <span class="type">String</span> <span class="variable">NAME</span> <span class="operator">=</span> <span class="string">&quot;root&quot;</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="keyword">final</span> <span class="type">String</span> <span class="variable">PWD</span> <span class="operator">=</span> <span class="string">&quot;l20031220&quot;</span>;</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 1. 注册驱动(注册一次)</span></span><br><span class="line">    <span class="keyword">static</span> &#123;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            Class.forName(DRIVER);</span><br><span class="line">        &#125; <span class="keyword">catch</span> (ClassNotFoundException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 2. 数据库连接</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> Connection <span class="title function_">getConnection</span><span class="params">()</span> <span class="keyword">throws</span> SQLException &#123;</span><br><span class="line">        <span class="keyword">return</span> DriverManager.getConnection(URL, NAME, PWD);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 3. 释放资源</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">close</span><span class="params">(PreparedStatement pstemt, Connection conn)</span>&#123;</span><br><span class="line">        close(<span class="literal">null</span>, pstemt, conn); <span class="comment">//方法重用</span></span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">close</span><span class="params">(ResultSet res, PreparedStatement pstemt, Connection conn)</span> &#123;</span><br><span class="line">        <span class="keyword">if</span> (res != <span class="literal">null</span>) &#123;</span><br><span class="line">            <span class="keyword">try</span> &#123;</span><br><span class="line">                res.close();</span><br><span class="line">            &#125; <span class="keyword">catch</span> (SQLException throwables) &#123;</span><br><span class="line">                throwables.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">if</span> (pstemt != <span class="literal">null</span>) &#123;</span><br><span class="line">            <span class="keyword">try</span> &#123;</span><br><span class="line">                pstemt.close();</span><br><span class="line">            &#125; <span class="keyword">catch</span> (SQLException throwables) &#123;</span><br><span class="line">                throwables.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">if</span> (conn != <span class="literal">null</span>)&#123;</span><br><span class="line">            <span class="keyword">try</span> &#123;</span><br><span class="line">                conn.close();</span><br><span class="line">            &#125; <span class="keyword">catch</span> (SQLException throwables) &#123;</span><br><span class="line">                throwables.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>测试：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> ethanliu.jdbc;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> ethanliu.jdbc.utils.JdbcUtils;</span><br><span class="line"><span class="keyword">import</span> org.junit.Test;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.PreparedStatement;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"></span><br><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Author</span> EthanLiu 16693226842@163.com</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Date</span> 2024/5/5 12:26</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Project</span> MySQL</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Theme</span> JDBC工具类测试</span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">JdbcUtilsDemo1</span> &#123;</span><br><span class="line">    </span><br><span class="line">    <span class="meta">@Test</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">insertTest</span><span class="params">()</span> &#123;</span><br><span class="line"></span><br><span class="line">        <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">        <span class="type">PreparedStatement</span> <span class="variable">pstemt</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">        <span class="type">int</span> <span class="variable">res</span> <span class="operator">=</span> -<span class="number">1</span>;</span><br><span class="line">        </span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            conn = JdbcUtils.getConnection(); <span class="comment">//连接数据库</span></span><br><span class="line"></span><br><span class="line">            <span class="comment">//添加事务</span></span><br><span class="line">            conn.setAutoCommit(<span class="literal">false</span>);</span><br><span class="line"></span><br><span class="line">            <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;insert into Login(username, password) values (?, ?);&quot;</span>; <span class="comment">//编写SQL(带占位符)</span></span><br><span class="line">            pstemt = conn.prepareStatement(sql); <span class="comment">//数据库执行对象</span></span><br><span class="line">            <span class="comment">//设置占位符的值</span></span><br><span class="line">            pstemt.setString(<span class="number">1</span>, <span class="string">&quot;liuaihui&quot;</span>);</span><br><span class="line">            pstemt.setString(<span class="number">2</span>, <span class="string">&quot;l20031220&quot;</span>);</span><br><span class="line"></span><br><span class="line">            <span class="comment">//添加数据</span></span><br><span class="line">            res = pstemt.executeUpdate(); <span class="comment">//返回受影响行数</span></span><br><span class="line">            <span class="comment">//执行DML语句时，执行后返回受影响的行数   受影响行数为0表示增、删、改没有成功</span></span><br><span class="line">            <span class="comment">//执行DDL语句，执行后返回0表示成功</span></span><br><span class="line">            </span><br><span class="line">            <span class="keyword">if</span> (res &gt; <span class="number">0</span>) &#123;</span><br><span class="line">                conn.commit();</span><br><span class="line">                System.out.println(<span class="string">&quot;添加成功&quot;</span>);</span><br><span class="line">            &#125;</span><br><span class="line"></span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">            <span class="keyword">try</span> &#123;</span><br><span class="line">                conn.rollback();</span><br><span class="line">                System.out.println(<span class="string">&quot;添加失败&quot;</span>);</span><br><span class="line">            &#125; <span class="keyword">catch</span> (SQLException throwables) &#123;</span><br><span class="line">                throwables.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">            </span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">            <span class="comment">//关闭资源</span></span><br><span class="line">            JdbcUtils.close(pstemt, conn);</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h2 id="三层开发业务的案例分析掌握">三层开发业务的案例分析(掌握)</h2>
<p>1、开发中，常使用分层思想</p>
<p>​ 1)
不同的层次结构分配不同的解决过程，各个层次间组成严密的封闭系统</p>
<p>​ 2)不同层级结构彼此平等</p>
<p>​ 3)分层的目的是：</p>
<p>​ a:解耦，就是降低代码之间的依赖关系。</p>
<p>​ b:可维护性，哪一层出现问题，直接维护哪一层。</p>
<p>​ c:可扩展性，哪一层需要添加代码，直接添加即可。</p>
<p>​ d:可重用性，一个方法可以被其它层重复调用。</p>
<p>2、不同层次，使用不同的包表示</p>
<p>​ 1）com.itheima.web web层 公司域名倒写。和前端页面直接交互。</p>
<p>​ 2）com.itheima.service
service层。也叫做业务层。专门用来处理业务的，比如事务。</p>
<p>​ 3）com.itheima.dao
dao层。数据处理层。操作数据库的代码都书写在这一层。直接和数据库交互。</p>
<p>​ 4）com.itheima.domain/bean/pojo javabean
存放实体类。临时存放数据</p>
<p>​ 5）com.itheima.utils 存放工具类。</p>
<p>具体的三层开发如下图所示：</p>
<figure>
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/%E4%B8%89%E5%B1%82%E5%BC%80%E5%8F%91%E6%B5%81%E7%A8%8B%E5%9B%BE.bmp" alt="三层开发流程图">
<figcaption aria-hidden="true">三层开发流程图</figcaption>
</figure>
<h1 id="第二章-连接池">第二章 连接池</h1>
<h2 id="连接池介绍">连接池介绍</h2>
<h3 id="目标-10">目标</h3>
<p>能够理解连接池的原理和好处</p>
<h3 id="讲解-11">讲解</h3>
<h4 id="没有连接池的现状">没有连接池的现状</h4>
<p>首先我们通过画图的形式来分析一下我们目前所学的jdbc程序的结构。</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/jdbc%E6%93%8D%E4%BD%9C%E6%95%B0%E6%8D%AE%E5%BA%93%E5%9B%BE%E8%A7%A3.bmp"></p>
<p>说明：以前使用的jdbc的缺点：</p>
<p>1、操作数据库都需要创建连接，操作完成还需要关闭连接</p>
<p>2、创建连接和关闭连接需要可能比执行sql需要的时间都长</p>
<p>3、一个网站需要高频繁的访问数据库，如果短时间频繁的访问数据库服务器，就容易造成服务器的宕机，即死机。</p>
<h4 id="连接池解决现状问题的原理">连接池解决现状问题的原理</h4>
<p>注意：</p>
<p>通过画图分析得出：当前的jdbc程序每次访问数据库都需要创建一个新的连接，访问完毕之后，还需要释放资源。那么在这样的一个过程中，连接的创建和销毁所消耗的资源是远远大于我们发送sql并执行的时间的。基于这样的情况，我们发现我们的jdbc程序将大量的资源浪费在了连接的创建和销毁上。</p>
<p>举例：就像在上海坐地铁，就一站2分钟的路程，往往在买地铁票的过程需要等待至少10分钟以上的时间。这样是不合理的。所以我们
需要对这样的结构进行优化。</p>
<p>思考上面的结构，大部分的时间浪费在了创建和销毁上。那么我们能不能实现将这些连接回收和利用呢？这样我们就不需要不停的创建和销毁了。只需要创建一次，放在指定的地方。当我们使用的时候，直接从里面拿就行了。用完放回原来的地方。不去销毁，当我再次使用的时候，去拿就行了。而这样的解决方案就是我们需要的。</p>
<p>优化后的结构如下：</p>
<p>说明：首先创建一定数量的连接，然后放到指定的地方。当我们需要获取连接的时候，直接从指定的地方获取。用完了，我们再将连接放回去。这样就能将我们连接的回收利用。并且不用花费大量时间在创建和销毁连接上。</p>
<p>解决方案如下图所示：</p>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0%E5%8E%9F%E7%90%86.bmp"></p>
<h4 id="连接池好处">连接池好处</h4>
<p>连接池中保存了一些数据库连接，这些连接是可以重复使用的。节省数据库的资源消耗。</p>
<h4 id="常用连接池的介绍">常用连接池的介绍</h4>
<p><code>javax.sql.DataSource</code>表示数据库<strong>连接池</strong>，DataSource本身只是Sun公司提供的一个接口,没有具体的实现，它的实现由连接池的数据库厂商去实现。我们只需要学习这个工具如何使用即可。</p>
<p>该接口如下：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">interface</span> <span class="title class_">DataSource</span> &#123;</span><br><span class="line">	Connection <span class="title function_">getConnection</span><span class="params">()</span>;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>常用的连接池实现组件有以下这些：</p>
<ol type="1">
<li><strong>阿里巴巴-德鲁伊Druid连接池：Druid是阿里巴巴开源平台上的一个项目，整个项目由数据库连接池、插件框架和SQL解析器组成。该项目主要是为了扩展JDBC的一些限制，可以让程序员实现一些特殊的需求。</strong></li>
<li><strong>C3P0是一个开源的JDBC连接池，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。C3P0有自动回收空闲连接功能。</strong></li>
<li>DBCP(DataBase Connection
Pool)数据库连接池，是Apache上的一个Java连接池项目。dbcp没有自动回收空闲连接的功能。</li>
</ol>
<h3 id="小结-8">小结</h3>
<p>连接池的好处？
连接池内部会保存好一些连接,这些连接可以反复使用,提高连接的使用率,降低数据库资源消耗
连接池的原理? 1.创建连接池时,连接池内部就会创建一些连接
2.当需要使用连接时,就直接从连接池里面取出连接
3.当连接使用完毕时,重新放回连接池</p>
<h2 id="druid连接池">Druid连接池</h2>
<h3 id="目标-11">目标</h3>
<p>能够掌握Druid连接池的使用</p>
<h3 id="讲解-12">讲解</h3>
<h4 id="druid简介">DRUID简介</h4>
<p>​
Druid是阿里巴巴开发的号称为监控而生的数据库连接池(可以监控访问数据库的性能)，Druid是目前最好的数据库连接池。在功能、性能、扩展性方面，都超过其他数据库连接池。Druid已经在阿里巴巴部署了超过600个应用，经过一年多生产环境大规模部署的严苛考验。如：一年一度的双十一活动，每年春运的抢火车票。</p>
<p>DRUID连接池使用的jar包：druid-1.0.9.jar</p>
<figure>
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/1550388331344.png" alt="1550388331344">
<figcaption aria-hidden="true">1550388331344</figcaption>
</figure>
<h4 id="druid常用的配置参数">Druid常用的配置参数</h4>
<table>
<colgroup>
<col style="width: 24%">
<col style="width: 75%">
</colgroup>
<thead>
<tr class="header">
<th><strong>url</strong></th>
<th><strong>数据库</strong>连接字符串<strong>jdbc:mysql://localhost:3306/数据库名</strong></th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td><strong>username</strong></td>
<td><strong>数据库的用户名</strong></td>
</tr>
<tr class="even">
<td><strong>password</strong></td>
<td><strong>数据库的密码</strong></td>
</tr>
<tr class="odd">
<td><strong>driverClassName</strong></td>
<td><strong>驱动类名。根据url自动识别，这一项可配可不配，如果不配置druid会根据url自动识别数据库的类型，然后选择相应的数据库驱动名</strong></td>
</tr>
<tr class="even">
<td><strong>initialSize</strong></td>
<td><strong>初始化时建立的物理连接的个数。初始化发生在显式调用init方法，或者第一次获取连接对象时</strong></td>
</tr>
<tr class="odd">
<td><strong>maxActive</strong></td>
<td><strong>连接池中最大连接数</strong></td>
</tr>
<tr class="even">
<td><strong>maxWait</strong></td>
<td><strong>获取连接时最长等待时间，单位是毫秒。</strong></td>
</tr>
</tbody>
</table>
<h4 id="druid连接池基本使用">Druid连接池基本使用</h4>
<h5 id="api介绍-4">API介绍</h5>
<p>核心类：<strong>DruidDataSourceFactory</strong></p>
<p>获取数据源的方法：使用<strong>com.alibaba.druid.pool.DruidDataSourceFactory</strong>类中的静态方法：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">static</span> javax.sql.DataSource <span class="title function_">createDataSource</span><span class="params">(Properties properties)</span></span><br><span class="line">创建一个连接池，连接池的参数使用properties中的数据</span><br></pre></td></tr></table></figure>
<p>配置信息在properties属性对象中。</p>
<p>我们可以看到Druid连接池在创建的时候需要一个Properties对象来设置参数，所以我们使用properties文件来保存对应的参数。</p>
<p>Druid连接池的配置文件名称随便，放到src目录或者项目根目录下面加载
<code>druid.properties</code>文件内容：</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 数据库连接参数</span></span><br><span class="line"><span class="attr">url</span>=<span class="string">jdbc:mysql://localhost:3306/day05_db</span></span><br><span class="line"><span class="attr">username</span>=<span class="string">root</span></span><br><span class="line"><span class="attr">password</span>=<span class="string">123</span></span><br><span class="line"><span class="attr">driverClassName</span>=<span class="string">com.mysql.jdbc.Driver</span></span><br></pre></td></tr></table></figure>
<h4 id="使用步骤-3">使用步骤</h4>
<ol type="1">
<li>导入核心包druid-1.0.9.jar</li>
</ol>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/11.bmp"></p>
<ol start="2" type="1">
<li>在项目下创建一个properties文件，文件名随意，设置对应参数</li>
</ol>
<p><img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/12.bmp"></p>
<ol start="3" type="1">
<li><p>加载properties文件的内容到Properties对象中</p></li>
<li><p>创建DRUID连接池，使用配置文件中的参数</p></li>
<li><p>从DRUID连接池中取出连接</p></li>
<li><p>执行SQL语句</p></li>
<li><p>关闭资源</p></li>
</ol>
<h4 id="案例代码-4">案例代码</h4>
<ol type="1">
<li>属性文件：在项目下新建一个druid配置文件，命名为：druid.properties</li>
</ol>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 数据库连接参数</span></span><br><span class="line"><span class="attr">url</span>=<span class="string">jdbc:mysql://localhost:3306/day05_db</span></span><br><span class="line"><span class="attr">username</span>=<span class="string">root</span></span><br><span class="line"><span class="attr">password</span>=<span class="string">123</span></span><br><span class="line"><span class="attr">driverClassName</span>=<span class="string">com.mysql.jdbc.Driver</span></span><br></pre></td></tr></table></figure>
<p>2.java代码</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">Demo03</span> &#123;</span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> Exception &#123;</span><br><span class="line">      <span class="comment">//加载properties文件的内容到Properties对象中</span></span><br><span class="line">        <span class="type">Properties</span> <span class="variable">info</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Properties</span>();</span><br><span class="line">        <span class="comment">//加载项目下的属性文件 相对项目根目录</span></span><br><span class="line"><span class="comment">//        FileInputStream fis = new FileInputStream(&quot;druid.properties&quot;);</span></span><br><span class="line">        <span class="comment">//相对src目录</span></span><br><span class="line">        <span class="type">InputStream</span> <span class="variable">fis</span> <span class="operator">=</span> Test01.class.getClassLoader().getResourceAsStream(<span class="string">&quot;druid.properties&quot;</span>);</span><br><span class="line">        <span class="comment">//从输入流中加载属性</span></span><br><span class="line">        info.load(fis);</span><br><span class="line">        System.out.println(info);</span><br><span class="line">        <span class="comment">//创建DRUID连接池，使用配置文件中的参数</span></span><br><span class="line">        <span class="type">DataSource</span> <span class="variable">dataSource</span> <span class="operator">=</span> DruidDataSourceFactory.createDataSource(info);</span><br><span class="line">        <span class="comment">//从DRUID连接池中取出连接</span></span><br><span class="line">        <span class="comment">//Connection conn = dataSource.getConnection();</span></span><br><span class="line">        <span class="comment">//System.out.println(&quot;conn = &quot; + conn);</span></span><br><span class="line"><span class="comment">// 需求: 根据用户名和密码 查询用户信息</span></span><br><span class="line">        <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">        <span class="type">PreparedStatement</span> <span class="variable">pstmt</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">        <span class="type">ResultSet</span> <span class="variable">rs</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            <span class="comment">// 获得连接</span></span><br><span class="line">            conn = dataSource.getConnection();</span><br><span class="line">            <span class="comment">// 获得发送sql的对象</span></span><br><span class="line">            <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;select * from emp where name=? and city=?&quot;</span>;</span><br><span class="line">            pstmt = conn.prepareStatement(sql);</span><br><span class="line">            <span class="comment">// 如果有问号,需要 设置参数,注意:下标从1开始</span></span><br><span class="line">            pstmt.setString(<span class="number">1</span>, <span class="string">&quot;刘备&quot;</span>);</span><br><span class="line">            pstmt.setString(<span class="number">2</span>, <span class="string">&quot;北京&quot;</span>);</span><br><span class="line">            <span class="comment">// 执行sql 获得结果</span></span><br><span class="line">            rs = pstmt.executeQuery();</span><br><span class="line">            <span class="comment">// 处理结果</span></span><br><span class="line">            <span class="keyword">if</span> (rs.next()) &#123;</span><br><span class="line">                <span class="type">int</span> <span class="variable">id</span> <span class="operator">=</span> rs.getInt(<span class="string">&quot;id&quot;</span>);</span><br><span class="line">                <span class="type">String</span> <span class="variable">name</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;name&quot;</span>);</span><br><span class="line">                <span class="type">String</span> <span class="variable">city</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;city&quot;</span>);</span><br><span class="line"></span><br><span class="line">                System.out.println(id + <span class="string">&quot;:::&quot;</span> + name + <span class="string">&quot;===&quot;</span> + city);</span><br><span class="line">            &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">                System.out.println(<span class="string">&quot;没有查到对应的用户信息!&quot;</span>);</span><br><span class="line">            &#125;</span><br><span class="line">        &#125; <span class="keyword">catch</span> (Exception e) &#123;</span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line"><span class="comment">//            JDBCUtils.release(conn, pstmt, rs);</span></span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h4 id="案例效果-1">案例效果</h4>
<figure>
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/image-20200609205210845.png" alt="image-20200609205210845">
<figcaption aria-hidden="true">image-20200609205210845</figcaption>
</figure>
<h1 id="第三章-jdbc练习">第三章 JDBC练习</h1>
<h2 id="需求">需求</h2>
<p>完成商品品牌数据的增删改查操作。</p>
<p>•查询：查询所有数据</p>
<p>•添加：添加品牌</p>
<p>•修改：根据 id修改</p>
<p>•删除：根据 id删除</p>
<h2 id="环境搭建">环境搭建</h2>
<p>•准备环境：1.创建项目 2.导入druid 和mysql驱动包
3.在src下面创建连接数据库的配置文件</p>
<p>Ø数据库表 tb_brand</p>
<p>Ø实体类 Brand</p>
<p>Ø测试用例</p>
<figure>
<img data-src="https://gitee.com/ethanliu6/ImgsForMe/raw/master/blog_images/image-20210917112318189.png" alt="image-20210917112318189">
<figcaption aria-hidden="true">image-20210917112318189</figcaption>
</figure>
<h2 id="查询所有数据">查询所有数据</h2>
<p>1.获取Connection</p>
<p>2.定义SQL：select *** from tb_brand;</p>
<p>3.获取 PreparedStatement对象</p>
<p>4.设置参数：不需要</p>
<p>5.执行SQL</p>
<p>6.处理结果：List<Brand></Brand></p>
<p>7.释放资源</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Test</span></span><br><span class="line">   <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">query</span><span class="params">()</span> <span class="keyword">throws</span> Exception&#123;</span><br><span class="line">       <span class="type">Properties</span> <span class="variable">p</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Properties</span>();</span><br><span class="line">       p.load(<span class="keyword">new</span> <span class="title class_">FileInputStream</span>(<span class="string">&quot;src\\druid.properties&quot;</span>));</span><br><span class="line">       <span class="type">DataSource</span> <span class="variable">ds</span> <span class="operator">=</span> DruidDataSourceFactory.createDataSource(p);</span><br><span class="line">       <span class="comment">//获取Connection</span></span><br><span class="line">       <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> ds.getConnection();</span><br><span class="line">       <span class="comment">//定义SQL：select * from tb_brand;</span></span><br><span class="line">       <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;select * from tb_brand&quot;</span>;</span><br><span class="line">       <span class="comment">//获取 PreparedStatement对象</span></span><br><span class="line">       <span class="type">PreparedStatement</span> <span class="variable">pst</span> <span class="operator">=</span> conn.prepareStatement(sql);</span><br><span class="line">       <span class="comment">//设置参数：不需要</span></span><br><span class="line">       <span class="comment">//执行SQL</span></span><br><span class="line">       <span class="type">ResultSet</span> <span class="variable">rs</span> <span class="operator">=</span> pst.executeQuery();</span><br><span class="line">       <span class="comment">//处理结果：List&lt;Brand&gt;</span></span><br><span class="line">       ArrayList&lt;Brand&gt; list = <span class="keyword">new</span> <span class="title class_">ArrayList</span>&lt;&gt;();</span><br><span class="line">       <span class="keyword">while</span> (rs.next())&#123;</span><br><span class="line">           <span class="type">int</span> <span class="variable">id</span> <span class="operator">=</span> rs.getInt(<span class="string">&quot;id&quot;</span>);</span><br><span class="line">           <span class="type">String</span> <span class="variable">brandName</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;brand_name&quot;</span>);</span><br><span class="line">           <span class="type">String</span> <span class="variable">companyName</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;company_name&quot;</span>);</span><br><span class="line">           <span class="type">int</span> <span class="variable">ordered</span> <span class="operator">=</span> rs.getInt(<span class="string">&quot;ordered&quot;</span>);</span><br><span class="line">           <span class="type">String</span> <span class="variable">description</span> <span class="operator">=</span> rs.getString(<span class="string">&quot;description&quot;</span>);</span><br><span class="line">           <span class="type">int</span> <span class="variable">status</span> <span class="operator">=</span> rs.getInt(<span class="string">&quot;status&quot;</span>);</span><br><span class="line">           <span class="type">Brand</span> <span class="variable">brand</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Brand</span>();</span><br><span class="line">           brand.setId(id);</span><br><span class="line">           brand.setBrandName(brandName);</span><br><span class="line">           brand.setCompanyName(companyName);</span><br><span class="line">           brand.setOrdered(ordered);</span><br><span class="line">           brand.setDescription(description);</span><br><span class="line">           brand.setStatus(status);</span><br><span class="line">           list.add(brand);</span><br><span class="line">       &#125;</span><br><span class="line">       System.out.println(<span class="string">&quot;list = &quot;</span> + list);</span><br><span class="line">       <span class="comment">//释放资源</span></span><br><span class="line">       rs.close();</span><br><span class="line">       pst.close();</span><br><span class="line">       conn.close();</span><br><span class="line">   &#125;</span><br></pre></td></tr></table></figure>
<h2 id="添加">添加</h2>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Test</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">add</span><span class="params">()</span> <span class="keyword">throws</span> Exception&#123;</span><br><span class="line">        <span class="type">Properties</span> <span class="variable">p</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Properties</span>();</span><br><span class="line">        p.load(<span class="keyword">new</span> <span class="title class_">FileInputStream</span>(<span class="string">&quot;src\\druid.properties&quot;</span>));</span><br><span class="line">        <span class="type">DataSource</span> <span class="variable">ds</span> <span class="operator">=</span> DruidDataSourceFactory.createDataSource(p);</span><br><span class="line">        <span class="comment">//获取Connection</span></span><br><span class="line">        <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> ds.getConnection();</span><br><span class="line">        <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;insert into tb_brand values(null,?,?,?,?,?)&quot;</span>;</span><br><span class="line">        <span class="comment">//获取 PreparedStatement对象</span></span><br><span class="line">        <span class="type">PreparedStatement</span> <span class="variable">pst</span> <span class="operator">=</span> conn.prepareStatement(sql);</span><br><span class="line">        pst.setString(<span class="number">1</span>,<span class="string">&quot;苹果&quot;</span>);</span><br><span class="line">        pst.setString(<span class="number">2</span>,<span class="string">&quot;apple&quot;</span>);</span><br><span class="line">        pst.setInt(<span class="number">3</span>,<span class="number">1</span>);</span><br><span class="line">        pst.setString(<span class="number">4</span>,<span class="string">&quot;贵&quot;</span>);</span><br><span class="line">        pst.setInt(<span class="number">5</span>,<span class="number">0</span>);</span><br><span class="line">        <span class="type">int</span> <span class="variable">count</span> <span class="operator">=</span> pst.executeUpdate();</span><br><span class="line">        System.out.println(<span class="string">&quot;count = &quot;</span> + count);</span><br><span class="line">        <span class="comment">//释放资源</span></span><br><span class="line">        pst.close();</span><br><span class="line">        conn.close();</span><br><span class="line">    &#125;</span><br></pre></td></tr></table></figure>
<h2 id="修改">修改</h2>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Test</span></span><br><span class="line">   <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">update</span><span class="params">()</span> <span class="keyword">throws</span> Exception&#123;</span><br><span class="line">       <span class="type">Properties</span> <span class="variable">p</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Properties</span>();</span><br><span class="line">       p.load(<span class="keyword">new</span> <span class="title class_">FileInputStream</span>(<span class="string">&quot;src\\druid.properties&quot;</span>));</span><br><span class="line">       <span class="type">DataSource</span> <span class="variable">ds</span> <span class="operator">=</span> DruidDataSourceFactory.createDataSource(p);</span><br><span class="line">       <span class="comment">//获取Connection</span></span><br><span class="line">       <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> ds.getConnection();</span><br><span class="line">       <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;update tb_brand set brand_name  = ?,company_name= ?,ordered= ?,description = ?,status= ? where id = ?&quot;</span>;</span><br><span class="line">       <span class="comment">//获取 PreparedStatement对象</span></span><br><span class="line">       <span class="type">PreparedStatement</span> <span class="variable">pst</span> <span class="operator">=</span> conn.prepareStatement(sql);</span><br><span class="line">       pst.setString(<span class="number">1</span>,<span class="string">&quot;OPPO&quot;</span>);</span><br><span class="line">       pst.setString(<span class="number">2</span>,<span class="string">&quot;OPPO&quot;</span>);</span><br><span class="line">       pst.setInt(<span class="number">3</span>,<span class="number">1</span>);</span><br><span class="line">       pst.setString(<span class="number">4</span>,<span class="string">&quot;贵&quot;</span>);</span><br><span class="line">       pst.setInt(<span class="number">5</span>,<span class="number">0</span>);</span><br><span class="line">       pst.setInt(<span class="number">6</span>,<span class="number">3</span>);</span><br><span class="line">       <span class="type">int</span> <span class="variable">count</span> <span class="operator">=</span> pst.executeUpdate();</span><br><span class="line">       System.out.println(<span class="string">&quot;count = &quot;</span> + count);</span><br><span class="line">       <span class="comment">//释放资源</span></span><br><span class="line">       pst.close();</span><br><span class="line">       conn.close();</span><br><span class="line">   &#125;</span><br></pre></td></tr></table></figure>
<h2 id="删除">删除</h2>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Test</span></span><br><span class="line">   <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">delete</span><span class="params">()</span> <span class="keyword">throws</span> Exception&#123;</span><br><span class="line">       <span class="type">Properties</span> <span class="variable">p</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Properties</span>();</span><br><span class="line">       p.load(<span class="keyword">new</span> <span class="title class_">FileInputStream</span>(<span class="string">&quot;src\\druid.properties&quot;</span>));</span><br><span class="line">       <span class="type">DataSource</span> <span class="variable">ds</span> <span class="operator">=</span> DruidDataSourceFactory.createDataSource(p);</span><br><span class="line">       <span class="comment">//获取Connection</span></span><br><span class="line">       <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> ds.getConnection();</span><br><span class="line">       <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;delete from tb_brand where id = ?&quot;</span>;</span><br><span class="line">       <span class="comment">//获取 PreparedStatement对象</span></span><br><span class="line">       <span class="type">PreparedStatement</span> <span class="variable">pst</span> <span class="operator">=</span> conn.prepareStatement(sql);</span><br><span class="line">       pst.setInt(<span class="number">1</span>,<span class="number">4</span>);</span><br><span class="line">       <span class="type">int</span> <span class="variable">count</span> <span class="operator">=</span> pst.executeUpdate();</span><br><span class="line">       System.out.println(<span class="string">&quot;count = &quot;</span> + count);</span><br><span class="line">       <span class="comment">//释放资源</span></span><br><span class="line">       pst.close();</span><br><span class="line">       conn.close();</span><br><span class="line">   &#125;</span><br></pre></td></tr></table></figure>
<h2 id="综合案例">综合案例</h2>
<h3 id="sql中的brand表">SQL中的Brand表</h3>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line">create database db5;</span><br><span class="line">use db5;</span><br><span class="line"></span><br><span class="line">-- 删除tb_brand表</span><br><span class="line">drop table if exists tb_brand;</span><br><span class="line">-- 创建tb_brand表</span><br><span class="line">create table tb_brand</span><br><span class="line">(</span><br><span class="line">    -- id 主键</span><br><span class="line">    id           int primary key auto_increment,</span><br><span class="line">    -- 品牌名称</span><br><span class="line">    brand_name   varchar(20),</span><br><span class="line">    -- 企业名称</span><br><span class="line">    company_name varchar(20),</span><br><span class="line">    -- 排序字段</span><br><span class="line">    ordered      int,</span><br><span class="line">    -- 描述信息</span><br><span class="line">    description  varchar(100),</span><br><span class="line">    -- 状态：0：禁用  1：启用</span><br><span class="line">    status       int</span><br><span class="line">);</span><br><span class="line">-- 添加数据</span><br><span class="line">insert into tb_brand (brand_name, company_name, ordered, description, status)</span><br><span class="line">values (&#x27;三只松鼠&#x27;, &#x27;三只松鼠股份有限公司&#x27;, 5, &#x27;好吃不上火&#x27;, 0),</span><br><span class="line">       (&#x27;华为&#x27;, &#x27;华为技术有限公司&#x27;, 100, &#x27;华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界&#x27;, 1),</span><br><span class="line">       (&#x27;小米&#x27;, &#x27;小米科技有限公司&#x27;, 50, &#x27;are you ok&#x27;, 1);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">SELECT id, brand_name, company_name, ordered, description, status FROM tb_brand;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="brand实体类">Brand实体类</h3>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> ethanliu.example;</span><br><span class="line"></span><br><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Author</span> EthanLiu 16693226842@163.com</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Date</span> 2024/5/5 14:47</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Project</span> MySQL</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Theme</span> 实体类</span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"></span><br><span class="line"><span class="comment">//实体类</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">Brand</span> &#123;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> <span class="type">int</span> id;</span><br><span class="line">    <span class="keyword">private</span> String brandName;</span><br><span class="line">    <span class="keyword">private</span> String companyName;</span><br><span class="line">    <span class="keyword">private</span> <span class="type">int</span> ordered;</span><br><span class="line">    <span class="keyword">private</span> String description;</span><br><span class="line">    <span class="keyword">private</span> <span class="type">int</span> status;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="title function_">Brand</span><span class="params">()</span> &#123;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="type">int</span> <span class="title function_">getId</span><span class="params">()</span> &#123;</span><br><span class="line">        <span class="keyword">return</span> id;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">setId</span><span class="params">(<span class="type">int</span> id)</span> &#123;</span><br><span class="line">        <span class="built_in">this</span>.id = id;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> String <span class="title function_">getBrandName</span><span class="params">()</span> &#123;</span><br><span class="line">        <span class="keyword">return</span> brandName;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">setBrandName</span><span class="params">(String brandName)</span> &#123;</span><br><span class="line">        <span class="built_in">this</span>.brandName = brandName;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> String <span class="title function_">getCompanyName</span><span class="params">()</span> &#123;</span><br><span class="line">        <span class="keyword">return</span> companyName;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">setCompanyName</span><span class="params">(String companyName)</span> &#123;</span><br><span class="line">        <span class="built_in">this</span>.companyName = companyName;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="type">int</span> <span class="title function_">getOrdered</span><span class="params">()</span> &#123;</span><br><span class="line">        <span class="keyword">return</span> ordered;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">setOrdered</span><span class="params">(<span class="type">int</span> ordered)</span> &#123;</span><br><span class="line">        <span class="built_in">this</span>.ordered = ordered;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> String <span class="title function_">getDescription</span><span class="params">()</span> &#123;</span><br><span class="line">        <span class="keyword">return</span> description;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">setDescription</span><span class="params">(String description)</span> &#123;</span><br><span class="line">        <span class="built_in">this</span>.description = description;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="type">int</span> <span class="title function_">getStatus</span><span class="params">()</span> &#123;</span><br><span class="line">        <span class="keyword">return</span> status;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">setStatus</span><span class="params">(<span class="type">int</span> status)</span> &#123;</span><br><span class="line">        <span class="built_in">this</span>.status = status;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="title function_">Brand</span><span class="params">(<span class="type">int</span> id, String brandName, String companyName, <span class="type">int</span> ordered, String description, <span class="type">int</span> status)</span> &#123;</span><br><span class="line">        <span class="built_in">this</span>.id = id;</span><br><span class="line">        <span class="built_in">this</span>.brandName = brandName;</span><br><span class="line">        <span class="built_in">this</span>.companyName = companyName;</span><br><span class="line">        <span class="built_in">this</span>.ordered = ordered;</span><br><span class="line">        <span class="built_in">this</span>.description = description;</span><br><span class="line">        <span class="built_in">this</span>.status = status;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">public</span> <span class="title function_">Brand</span><span class="params">(String brandName, String companyName, <span class="type">int</span> ordered, String description, <span class="type">int</span> status)</span> &#123;</span><br><span class="line">        <span class="built_in">this</span>.brandName = brandName;</span><br><span class="line">        <span class="built_in">this</span>.companyName = companyName;</span><br><span class="line">        <span class="built_in">this</span>.ordered = ordered;</span><br><span class="line">        <span class="built_in">this</span>.description = description;</span><br><span class="line">        <span class="built_in">this</span>.status = status;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> String <span class="title function_">toString</span><span class="params">()</span> &#123;</span><br><span class="line">        <span class="keyword">return</span> <span class="string">&quot;Brand&#123;&quot;</span> +</span><br><span class="line">                <span class="string">&quot;id=&quot;</span> + id +</span><br><span class="line">                <span class="string">&quot;, brandName=&#x27;&quot;</span> + brandName + <span class="string">&#x27;\&#x27;&#x27;</span> +</span><br><span class="line">                <span class="string">&quot;, commpanyName=&#x27;&quot;</span> + companyName + <span class="string">&#x27;\&#x27;&#x27;</span> +</span><br><span class="line">                <span class="string">&quot;, ordered=&quot;</span> + ordered +</span><br><span class="line">                <span class="string">&quot;, description=&#x27;&quot;</span> + description + <span class="string">&#x27;\&#x27;&#x27;</span> +</span><br><span class="line">                <span class="string">&quot;, status=&quot;</span> + status +</span><br><span class="line">                <span class="string">&#x27;&#125;&#x27;</span>;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h3 id="branddao类">BrandDao类</h3>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> ethanliu.example;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> com.alibaba.druid.pool.DruidDataSourceFactory;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> javax.sql.DataSource;</span><br><span class="line"><span class="keyword">import</span> java.io.FileReader;</span><br><span class="line"><span class="keyword">import</span> java.io.IOException;</span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.PreparedStatement;</span><br><span class="line"><span class="keyword">import</span> java.sql.ResultSet;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">import</span> java.util.ArrayList;</span><br><span class="line"><span class="keyword">import</span> java.util.List;</span><br><span class="line"><span class="keyword">import</span> java.util.Properties;</span><br><span class="line"></span><br><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Author</span> EthanLiu 16693226842@163.com</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Date</span> 2024/5/5 15:33</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Project</span> MySQL</span></span><br><span class="line"><span class="comment"> * <span class="doctag">@Theme</span> xxx</span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"><span class="comment">//Dao类</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">BrandDao</span> &#123;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="type">Properties</span> <span class="variable">prop</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="type">Connection</span> <span class="variable">conn</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="type">DataSource</span> <span class="variable">ds</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="type">PreparedStatement</span> <span class="variable">pstmt</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="type">ResultSet</span> <span class="variable">res</span> <span class="operator">=</span> <span class="literal">null</span>;</span><br><span class="line">    <span class="keyword">private</span> <span class="keyword">static</span> <span class="type">int</span> <span class="variable">resStatue</span> <span class="operator">=</span> -<span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">    <span class="comment">// 建立连接池对象并建立连接</span></span><br><span class="line">    <span class="keyword">static</span> &#123;</span><br><span class="line">        <span class="comment">//数据库连接</span></span><br><span class="line">        prop = <span class="keyword">new</span> <span class="title class_">Properties</span>();</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            prop.load(<span class="keyword">new</span> <span class="title class_">FileReader</span>(<span class="string">&quot;src/jdbc.properties&quot;</span>));</span><br><span class="line">        &#125; <span class="keyword">catch</span> (IOException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="comment">// 建立连接池对象, 使用配置文件参数</span></span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            ds = DruidDataSourceFactory.createDataSource(prop);</span><br><span class="line">        &#125; <span class="keyword">catch</span> (Exception e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            conn = ds.getConnection(); <span class="comment">//建立连接对象</span></span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            conn.setAutoCommit(<span class="literal">false</span>); <span class="comment">//开启手动提交事务</span></span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">//1. 全部查询</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> List&lt;Brand&gt; <span class="title function_">findAll</span><span class="params">()</span> <span class="keyword">throws</span> SQLException &#123;</span><br><span class="line">        sql = <span class="string">&quot;SELECT id, brand_name, company_name, ordered, description, status FROM tb_brand;&quot;</span>;</span><br><span class="line">        pstmt = conn.prepareStatement(sql);</span><br><span class="line">        <span class="type">ResultSet</span> <span class="variable">res</span> <span class="operator">=</span> pstmt.executeQuery();</span><br><span class="line">        conn.commit();</span><br><span class="line"></span><br><span class="line">        <span class="comment">//处理查询结果</span></span><br><span class="line">        List&lt;Brand&gt; brands = <span class="keyword">new</span> <span class="title class_">ArrayList</span>&lt;&gt;();</span><br><span class="line">        <span class="keyword">while</span> (res != <span class="literal">null</span> &amp;&amp; res.next()) &#123;</span><br><span class="line">            <span class="type">int</span> <span class="variable">id</span> <span class="operator">=</span> res.getInt(<span class="string">&quot;id&quot;</span>);</span><br><span class="line">            <span class="type">String</span> <span class="variable">brandName</span> <span class="operator">=</span> res.getString(<span class="string">&quot;brand_name&quot;</span>);</span><br><span class="line">            <span class="type">String</span> <span class="variable">companyName</span> <span class="operator">=</span> res.getString(<span class="string">&quot;company_name&quot;</span>);</span><br><span class="line">            <span class="type">int</span> <span class="variable">ordered</span> <span class="operator">=</span> res.getInt(<span class="string">&quot;ordered&quot;</span>);</span><br><span class="line">            <span class="type">String</span> <span class="variable">description</span> <span class="operator">=</span> res.getString(<span class="string">&quot;description&quot;</span>);</span><br><span class="line">            <span class="type">int</span> <span class="variable">status</span> <span class="operator">=</span> res.getInt(<span class="string">&quot;status&quot;</span>);</span><br><span class="line"></span><br><span class="line">            <span class="comment">//创建Brand对象</span></span><br><span class="line">            <span class="type">Brand</span> <span class="variable">brand</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Brand</span>(id, brandName, companyName, ordered, description, status);</span><br><span class="line">            brands.add(brand);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//关闭资源</span></span><br><span class="line">        &#123;</span><br><span class="line">            res.close();</span><br><span class="line">            pstmt.close();</span><br><span class="line">            conn.close();</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">return</span> brands;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">//2. 添加</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">addBrand</span><span class="params">(Brand brand)</span> <span class="keyword">throws</span> SQLException &#123;</span><br><span class="line"></span><br><span class="line">        <span class="type">int</span> <span class="variable">id</span> <span class="operator">=</span> brand.getId();</span><br><span class="line">        <span class="type">String</span> <span class="variable">brandName</span> <span class="operator">=</span> brand.getBrandName();</span><br><span class="line">        <span class="type">String</span> <span class="variable">companyName</span> <span class="operator">=</span> brand.getCompanyName();</span><br><span class="line">        <span class="type">int</span> <span class="variable">ordered</span> <span class="operator">=</span> brand.getOrdered();</span><br><span class="line">        <span class="type">String</span> <span class="variable">description</span> <span class="operator">=</span> brand.getDescription();</span><br><span class="line">        <span class="type">int</span> <span class="variable">status</span> <span class="operator">=</span> brand.getStatus();</span><br><span class="line"></span><br><span class="line">        sql = <span class="string">&quot;insert into tb_brand(id, brand_name, company_name, ordered, description, status) values(null, ?, ?, ?, ?, ?);&quot;</span>;</span><br><span class="line">        pstmt = conn.prepareStatement(sql);</span><br><span class="line"></span><br><span class="line">        pstmt.setString(<span class="number">1</span>, brandName);</span><br><span class="line">        pstmt.setString(<span class="number">2</span>, companyName);</span><br><span class="line">        pstmt.setInt(<span class="number">3</span>, ordered);</span><br><span class="line">        pstmt.setString(<span class="number">4</span>, description);</span><br><span class="line">        pstmt.setInt(<span class="number">5</span>, status);</span><br><span class="line"></span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line"><span class="comment">//            pstmt = conn.prepareStatement(sql);</span></span><br><span class="line">            resStatue = pstmt.executeUpdate();</span><br><span class="line">            <span class="keyword">if</span> (resStatue &gt; <span class="number">0</span>) &#123;</span><br><span class="line">                conn.commit();</span><br><span class="line">                System.out.println(<span class="string">&quot;添加成功&quot;</span>);</span><br><span class="line">            &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">                System.out.println(<span class="string">&quot;未添加任何数据&quot;</span>);</span><br><span class="line">            &#125;</span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">            conn.rollback();</span><br><span class="line">            System.out.println(<span class="string">&quot;添加失败!\n失败原因: &quot;</span> + e.getMessage());</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">            <span class="comment">//关闭资源</span></span><br><span class="line">            pstmt.close();</span><br><span class="line">            conn.close();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">//3. 删除</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">deleteBrand</span><span class="params">(<span class="type">int</span> id, String brandName)</span> <span class="keyword">throws</span> SQLException &#123;</span><br><span class="line"></span><br><span class="line">        sql = <span class="string">&quot;delete from tb_brand where id = ? and brand_name=?&quot;</span>;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            <span class="comment">//获取 PreparedStatement对象</span></span><br><span class="line">            pstmt = conn.prepareStatement(sql);</span><br><span class="line">            pstmt.setInt(<span class="number">1</span>, id);</span><br><span class="line">            pstmt.setString(<span class="number">2</span>, brandName);</span><br><span class="line">            resStatue = pstmt.executeUpdate();</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">            <span class="keyword">if</span> (resStatue &gt; <span class="number">0</span>) &#123;</span><br><span class="line">                conn.commit();</span><br><span class="line">                System.out.println(<span class="string">&quot;删除成功&quot;</span>);</span><br><span class="line">            &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">                System.out.println(<span class="string">&quot;未删除任何数据&quot;</span>);</span><br><span class="line">            &#125;</span><br><span class="line"></span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">            conn.rollback();</span><br><span class="line">            System.out.println(<span class="string">&quot;删除失败!\n失败原因: &quot;</span> + e.getMessage());</span><br><span class="line">            e.printStackTrace();</span><br><span class="line"></span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">            <span class="comment">//释放资源</span></span><br><span class="line">            pstmt.close();</span><br><span class="line">            conn.close();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">//4. 修改</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title function_">updateBrand</span><span class="params">(<span class="type">int</span> id, String brandName, Brand brand)</span> <span class="keyword">throws</span> SQLException &#123;</span><br><span class="line">        <span class="type">String</span> <span class="variable">newBrandName</span> <span class="operator">=</span> brand.getBrandName();</span><br><span class="line">        <span class="type">String</span> <span class="variable">companyName</span> <span class="operator">=</span> brand.getCompanyName();</span><br><span class="line">        <span class="type">int</span> <span class="variable">ordered</span> <span class="operator">=</span> brand.getOrdered();</span><br><span class="line">        <span class="type">String</span> <span class="variable">description</span> <span class="operator">=</span> brand.getDescription();</span><br><span class="line">        <span class="type">int</span> <span class="variable">status</span> <span class="operator">=</span> brand.getStatus();</span><br><span class="line"></span><br><span class="line">        <span class="type">String</span> <span class="variable">sql</span> <span class="operator">=</span> <span class="string">&quot;update tb_brand set brand_name  = ?,company_name= ?,ordered= ?,description = ?,status= ? where id = ? and brand_name = ?&quot;</span>;</span><br><span class="line"></span><br><span class="line">        pstmt = conn.prepareStatement(sql);</span><br><span class="line"></span><br><span class="line">        pstmt.setString(<span class="number">1</span>, newBrandName);</span><br><span class="line">        pstmt.setString(<span class="number">2</span>, companyName);</span><br><span class="line">        pstmt.setInt(<span class="number">3</span>, ordered);</span><br><span class="line">        pstmt.setString(<span class="number">4</span>, description);</span><br><span class="line">        pstmt.setInt(<span class="number">5</span>, status);</span><br><span class="line">        pstmt.setInt(<span class="number">6</span>, id);</span><br><span class="line">        pstmt.setString(<span class="number">7</span>, brandName);</span><br><span class="line"></span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            resStatue = pstmt.executeUpdate();</span><br><span class="line"></span><br><span class="line">            <span class="keyword">if</span> (resStatue &gt; <span class="number">0</span>) &#123;</span><br><span class="line">                conn.commit();</span><br><span class="line">                System.out.println(<span class="string">&quot;更改成功&quot;</span>);</span><br><span class="line">            &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">                System.out.println(<span class="string">&quot;未更改任何数据&quot;</span>);</span><br><span class="line">            &#125;</span><br><span class="line"></span><br><span class="line">        &#125; <span class="keyword">catch</span> (</span><br><span class="line">                SQLException e) &#123;</span><br><span class="line">            conn.rollback();</span><br><span class="line">            System.out.println(<span class="string">&quot;更改失败!\n失败原因: &quot;</span> + e.getMessage());</span><br><span class="line">            e.printStackTrace();</span><br><span class="line"></span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">            <span class="comment">//释放资源</span></span><br><span class="line">            pstmt.close();</span><br><span class="line">            conn.close();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h3 id="测试代码">测试代码</h3>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br></pre></td><td class="code"><pre><span class="line">package ethanliu.example;</span><br><span class="line"></span><br><span class="line">import org.junit.Test;</span><br><span class="line"></span><br><span class="line">import java.sql.SQLException;</span><br><span class="line">import java.util.List;</span><br><span class="line"></span><br><span class="line">/**</span><br><span class="line"> * @Author EthanLiu 16693226842@163.com</span><br><span class="line"> * @Date 2024/5/5 15:32</span><br><span class="line"> * @Project MySQL</span><br><span class="line"> * @Theme 综合案例测试代码</span><br><span class="line"> */</span><br><span class="line">public class BrandTest &#123;</span><br><span class="line"></span><br><span class="line">    @Test</span><br><span class="line">    public void brandDaoFindAll() throws SQLException &#123;</span><br><span class="line">        List&lt;Brand&gt; brands = BrandDao.findAll();</span><br><span class="line">        for (Brand brand : brands) &#123;</span><br><span class="line">            System.out.println(brand.toString());</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    @Test</span><br><span class="line">    public void brandDaoAddBrand() throws SQLException &#123;</span><br><span class="line">        Brand brand = new Brand(&quot;法拉利&quot;, &quot;法拉利科技有限公司&quot;, 12, &quot;要想跑得快, 必须法拉利&quot;, 1);</span><br><span class="line">            BrandDao.addBrand(brand);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    @Test</span><br><span class="line">    public void brandDaoDeleteBrand() throws SQLException &#123;</span><br><span class="line">        BrandDao.deleteBrand(7,&quot;法拉利&quot;);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    @Test</span><br><span class="line">    public void brandDaoUpdateBrand() throws SQLException &#123;</span><br><span class="line">        Brand brand = new Brand(&quot;小米&quot;, &quot;小米科技有限公司&quot;, 5, &quot;米家的东西伴你一生&quot;,1);</span><br><span class="line">        BrandDao.updateBrand(3, &quot;小米&quot;, brand);</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

    </div>

    
    
    

    <footer class="post-footer">
          <div class="reward-container">
  <div>请我一杯咖啡吧！</div>
  <button>
    赞赏
  </button>
  <div class="post-reward">
      <div>
        <img src="/images/wChat.png" alt="Ethan Liu 微信">
        <span>微信</span>
      </div>

  </div>
</div>

          

<div class="post-copyright">
<ul>
  <li class="post-copyright-author">
      <strong>本文作者： </strong>Ethan Liu
  </li>
  <li class="post-copyright-link">
      <strong>本文链接：</strong>
      <a href="http://ethanliu6.github.io/2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0&%E7%BB%BC%E5%90%88%E7%BB%83%E4%B9%A0/" title="MySQL之JDBC">http://ethanliu6.github.io/2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&数据库连接池&综合练习/</a>
  </li>
  <li class="post-copyright-license">
      <strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <span class="exturl" data-url="aHR0cHM6Ly9jcmVhdGl2ZWNvbW1vbnMub3JnL2xpY2Vuc2VzL2J5LW5jLXNhLzQuMC8="><i class="fab fa-fw fa-creative-commons"></i>BY-NC-SA</span> 许可协议。转载请注明出处！
  </li>
</ul>
</div>


          <div class="post-tags">
              <a href="/tags/Java/" rel="tag"><i class="fa fa-tag"></i> Java</a>
              <a href="/tags/MySQL/" rel="tag"><i class="fa fa-tag"></i> MySQL</a>
          </div>

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/2024/05/02/JavaNotes/JavaSE%E8%BF%9B%E9%98%B6/day13/JavaSE%E8%BF%9B%E9%98%B6%E2%80%94%E2%80%94-Junit%E6%B5%8B%E8%AF%95%E5%8D%95%E5%85%83/" rel="prev" title="JavaSE进阶—— Junit测试单元">
                  <i class="fa fa-angle-left"></i> JavaSE进阶—— Junit测试单元
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/2024/05/05/JavaNotes/MySQL/%E8%84%9A%E6%9C%ACSQL%E9%97%AE%E9%A2%98/" rel="next" title="MySQL之Errors">
                  MySQL之Errors <i class="fa fa-angle-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>






    <div class="comments" id="SOHUCS" sid="020300feda6e9b7c9ef118b4bbf6cd7c"></div>
</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">

  <div class="copyright">
    &copy; 2023-2024 – 
    <span itemprop="copyrightYear">2024</span>
    <span class="with-love">
      <i class="fa fa-heart"></i>
    </span>
    <span class="author" itemprop="copyrightHolder">Is Ethan's</span>
  </div>
<div class="wordcount">
  <span class="post-meta-item">
    <span class="post-meta-item-icon">
      <i class="fa fa-chart-line"></i>
    </span>
    <span title="站点总字数">113k</span>
  </span>
  <span class="post-meta-item">
    <span class="post-meta-item-icon">
      <i class="fa fa-coffee"></i>
    </span>
    <span title="站点阅读时长">6:51</span>
  </span>
</div>
<div class="busuanzi-count">
    <span class="post-meta-item" id="busuanzi_container_site_uv">
      <span class="post-meta-item-icon">
        <i class="fa fa-user"></i>
      </span>
      <span class="site-uv" title="总访客量">
        <span id="busuanzi_value_site_uv"></span>
      </span>
    </span>
    <span class="post-meta-item" id="busuanzi_container_site_pv">
      <span class="post-meta-item-icon">
        <i class="fa fa-eye"></i>
      </span>
      <span class="site-pv" title="总访问量">
        <span id="busuanzi_value_site_pv"></span>
      </span>
    </span>
</div>

    </div>
  </footer>

  
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up fa-lg"></i>
    <span>0%</span>
  </div>
  <div class="reading-progress-bar"></div>
  <a role="button" class="book-mark-link book-mark-link-fixed"></a>

  <span class="exturl github-corner" data-url="aHR0cHM6Ly9naXRodWIuY29tL0V0aGFuTGl1Ni8=" title="在 GitHub 上关注我" aria-label="在 GitHub 上关注我"><svg width="80" height="80" viewBox="0 0 250 250" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></span>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


  
  <script src="https://cdnjs.cloudflare.com/ajax/libs/animejs/3.2.1/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/next-theme-pjax/0.6.0/pjax.min.js" integrity="sha256-vxLn1tSKWD4dqbMRyv940UYw4sXgMtYcK6reefzZrao=" crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/fancyapps-ui/5.0.33/fancybox/fancybox.umd.js" integrity="sha256-+2+qOqR8CKoHh/AsVR9k2qaDBKWjYNC2nozhYmv5j9k=" crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/medium-zoom/1.1.0/medium-zoom.min.js" integrity="sha256-ZgMyDAIYDYGxbcpJcfUnYwNevG/xi9OHKaR/8GK+jWc=" crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/lozad.js/1.16.0/lozad.min.js" integrity="sha256-mOFREFhqmHeQbXpK2lp4nA3qooVgACfh88fpJftLBbc=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/next-boot.js"></script><script src="/js/bookmark.js"></script><script src="/js/pjax.js"></script>

  <script src="https://cdnjs.cloudflare.com/ajax/libs/hexo-generator-searchdb/1.4.1/search.js" integrity="sha256-1kfA5uHPf65M5cphT2dvymhkuyHPQp5A53EGZOnOLmc=" crossorigin="anonymous"></script>
<script src="/js/third-party/search/local-search.js"></script>

  <script class="next-config" data-name="pdf" type="application/json">{"object_url":{"url":"https://cdnjs.cloudflare.com/ajax/libs/pdfobject/2.2.12/pdfobject.min.js","integrity":"sha256-g2xji1rlE3KsGVClvuxTbcR0Kn2+wtQADSff2Tbb4zA="},"url":"/lib/pdf/web/viewer.html"}</script>
  <script src="/js/third-party/tags/pdf.js"></script>



  <script src="/js/third-party/fancybox.js"></script>



  
  <script data-pjax async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>




  

  <script class="next-config" data-name="enableMath" type="application/json">true</script><script class="next-config" data-name="mathjax" type="application/json">{"enable":true,"tags":"all","js":{"url":"https://cdnjs.cloudflare.com/ajax/libs/mathjax/3.2.2/es5/tex-mml-chtml.js","integrity":"sha256-MASABpB4tYktI2Oitl4t+78w/lyA+D7b/s9GEP0JOGI="}}</script>
<script src="/js/third-party/math/mathjax.js"></script>


  <script src="https://cdnjs.cloudflare.com/ajax/libs/quicklink/2.3.0/quicklink.umd.js" integrity="sha256-yvJQOINiH9fWemHn0vCA5lsHWJaHs6/ZmO+1Ft04SvM=" crossorigin="anonymous"></script>
  <script class="next-config" data-name="quicklink" type="application/json">{"enable":true,"home":true,"archive":true,"delay":true,"timeout":3000,"priority":true,"url":"http://ethanliu6.github.io/2024/05/03/JavaNotes/MySQL/day03/day03-JDBC&%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0&%E7%BB%BC%E5%90%88%E7%BB%83%E4%B9%A0/"}</script>
  <script src="/js/third-party/quicklink.js"></script>
<script class="next-config" data-name="changyan" type="application/json">{"enable":true,"appid":"cyxf6tytI","appkey":"90987f30c4b5248baad5a04c7a6abfe1","count":true}</script>
<script src="/js/third-party/comments/changyan.js"></script>

</body>
</html>
